SQL grouping by all the columns

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

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?

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.

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