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 *

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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 *.

Answer 7

Some databases support GROUP BY ALL, such as Snowflake (see documentation here).

GROUP BY ALL

Specifies that all items in the SELECT list that do not use aggregate functions should be used for grouping.

It's great to simplify some queries.

Before:

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, SUM(price)
FROM table
GROUP BY col1, col2, col3, col4, col5, col6, col7, col8, col9

AFTER:

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, SUM(price)
FROM table
GROUP BY ALL

Answer 8

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 <= (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(*) > 1  ') 

Answer 9

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