Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL grouping by all the columns

Tags:

sql

People also ask

How do I group all columns in a table?

Use an aggregate function to group by one or more columns Select Group by on the Home tab. Select the Advanced option, so you can select multiple columns to group by. Select the Country column. Select Add grouping.

Can you GROUP BY multiple columns at once?

We can group the resultset in SQL on multiple column values. When we define the grouping criteria on more than one column, all the records having the same value for the columns defined in the group by clause are collectively represented using a single record in the query output.

How do you group columns in SQL?

The SQL GROUP BY clause allows us to group individual data based on defined criteria. You can group individual data by one or more table columns. In order to do the grouping properly, you often need to apply aggregate functions to the column(s) within the SQL SELECT statement.

Do you have to GROUP BY all columns in SQL?

Introduction to SQL GROUP BY clause The GROUP BY is an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows based on values of one or more columns.


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

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

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.


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