[sql] SQL grouping by all the columns

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

select * from table group by *

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

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

He is trying find and display the duplicate rows in a table.

SELECT *, COUNT(*) AS NoOfOccurrences
FROM TableName GROUP BY *
HAVING COUNT(*) > 1

Do we have a simple way to accomplish this?

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

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

The DISTINCT Keyword

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

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

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  ') 

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.