SQL grouping by all the columns

Question

Is there any way to group by all the columns of a table without specifying the column names   Like   select   from table group by

User · Answer

I wanted to do counts and sums over full resultset  I achieved grouping by all with GROUP BY 1 1

User · Answer

You can use Group by All but be careful as Group by All will be removed from future versions of SQL server

User · Answer

Here is my suggestion   DECLARE  FIELDS VARCHAR MAX    NUM INT  --DROP TABLE  FIELD LIST  SET  NUM   1 SET  FIELDS       SELECT   SEQ    IDENTITY int 1 1    COLUMN NAME INTO  FIELD LIST FROM Req INFORMATION SCHEMA COLUMNS WHERE TABLE NAME   N new340B   WHILE  NUM  lt    SELECT COUNT    FROM  FIELD LIST  BEGIN SET  FIELDS    FIELDS          SELECT COLUMN NAME FROM  FIELD LIST WHERE SEQ    NUM  SET  NUM    NUM   1 END  SET  FIELDS   RIGHT  FIELDS LEN  FIELDS -1   EXEC  SELECT      FIELDS      COUNT    AS QTY FROM  Req   dbo   new340B  GROUP BY      FIELDS     HAVING COUNT     gt  1

User · Answer

If you are using SqlServer the distinct keyword should work for you   Not sure about other databases   declare  t table  a int   b int   insert into  t  a b  select 1  1 insert into  t  a b  select 1  2 insert into  t  a b  select 1  1  select distinct   from  t   results in  a b 1 1 1 2

User · Answer

He is trying find and display the duplicate rows in a table   SELECT    COUNT    AS NoOfOccurrences FROM TableName GROUP BY   HAVING COUNT     gt  1   Do we have a simple way to accomplish this

User · Answer

The DISTINCT Keyword I believe what you are trying to do is   SELECT DISTINCT   FROM MyFooTable    If you group by all columns  you are just requesting that duplicate data be removed   For example a table with the following data    id       value       ---- ----------------   1   foo   2   bar   1   foo   3   something else   If you perform the following query which is essentially the same as SELECT   FROM MyFooTable GROUP BY   if you are assuming   means all columns   SELECT   FROM MyFooTable GROUP BY id  value    id       value       ---- ----------------   1   foo   3   something else   2   bar   It removes all duplicate values  which essentially makes it semantically identical to using the DISTINCT keyword with the exception of the ordering of results  For example   SELECT DISTINCT   FROM MyFooTable    id       value       ---- ----------------   1   foo   2   bar   3   something else

User · Answer

nope   are you trying to do some aggregation   if so  you could do something like this to get what you need   with a as        select sum IntField  as Total      from Table      group by CharField   select    a Total from Table t inner join a on t Field a Field

User · Answer

Short answer  no   GROUP BY clauses intrinsically require order to the way they arrange your results   A different order of field groupings would lead to different results   Specifying a wildcard would leave the statement open to interpretation and unpredictable behaviour

User · Answer

No because this fundamentally means that you will not be grouping anything   If you group by all columns  and have a properly defined table w  a unique index  then SELECT   FROM table is essentially the same thing as SELECT   FROM table GROUP BY

[sql] SQL grouping by all the columns

Examples related to sql