Any reason for GROUP BY clause without aggregation function?

sql mysql group-by aggregate
Niels Bom picture Niels Bom · Feb 4, 2010 · Viewed 25.9k times · Source

I'm (thoroughly) learning SQL at the moment and came across the GROUP BYclause.

GROUP BY aggregates or groups the resultset according to the argument(s) you give it. If you use this clause in a query you can then perform aggregate functions on the resultset to find statistical information on the resultset like finding averages (AVG()) or frequency (COUNT()).

My question is: is the GROUP BY statement in any way useful without an accompanying aggregate function?

Update Using GROUP BY as a synonym for DISTINCT is (probably) a bad idea because I suspect it is slower.

Answer

OMG Ponies picture OMG Ponies · Feb 4, 2010

is the GROUP BY statement in any way useful without an accompanying aggregate function?

Using DISTINCT would be a synonym in such a situation, but the reason you'd want/have to define a GROUP BY clause would be in order to be able to define HAVING clause details.

If you need to define a HAVING clause, you have to define a GROUP BY - you can't do it in conjunction with DISTINCT.

Related questions

Can I use non-aggregate columns with group by?

You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More