CODEWITHSUNDEEP
sqlmysqlgroup-byaggregate
Niels Bom
Niels Bom

Reputation: 9377

Any reason for GROUP BY clause without aggregation function?

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.

Upvotes: 24

Views: 34286

Answers (5)

Quassnoi
Quassnoi

Reputation: 425251

Note: everything below only applies to MySQL

GROUP BY is guaranteed to return results in order, DISTINCT is not.

GROUP BY along with ORDER BY NULL is of same efficiency as DISTINCT (and implemented in the say way). If there is an index on the field being aggregated (or distinctified), both clauses use loose index scan over this field.

In GROUP BY, you can return non-grouped and non-aggregated expressions. MySQL will pick any random values from from the corresponding group to calculate the expression.

With GROUP BY, you can omit the GROUP BY expressions from the SELECT clause. With DISTINCT, you can't. Every row returned by a DISTINCT is guaranteed to be unique.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 13

Ashish Agarwal
Ashish Agarwal

Reputation: 6283

Group by can used in Two way Majorly
1)in conjunction with SQL aggregation functions
2)to eliminate duplicate rows from a result set

SO answer to your question lies in second part of USEs above described.

Upvotes: 6

Amirshk
Amirshk

Reputation: 8258

It is used for more then just aggregating functions.

For example, consider the following code:

SELECT product_name, MAX('last_purchased') FROM products GROUP BY product_name

This will return only 1 result per product, but with the latest updated value of that records.

Upvotes: -2

Adriaan Stander
Adriaan Stander

Reputation: 166326

You can perform a DISTINCT select by using a GROUP BY without any AGGREGATES.

Upvotes: 7

Related Questions