CODEWITHSUNDEEP
sqlt-sqlgroup-by
Jibin
Jibin

Reputation: 3102

Use of GROUP BY in SQL -- only with aggregates?

is GROUP BY used only with aggregate functions ? Can anyone give an example where it is used not in conjunction with aggregate functions.

Upvotes: 7

Views: 10829

Answers (2)

Andriy M
Andriy M

Reputation: 77677

SELECT with GROUP BY can be used as an alternative to SELECT DISTINCT. Pranay Rana's example is essentially equivalent to

SELECT DISTINCT UserName, Departmentid FROM user

If you want an example where the preference of GROUP BY over DISTINCT is justified, here's one. Say, you want to return items that only occur once in a table:

SELECT Item
FROM atable
GROUP BY Item
HAVING COUNT(*) = 1

As you can see, the query only returns a non-aggregated column.

It still uses an aggregate function, though, even if not in the SELECT clause. There may be situations where you must prefer GROUP BY to DISTINCT while not needing to use aggregate functions at all, but I'll have to be platform specific at this point.

In particular, if you are using SQL Server, you will find a difference between the results of the following two statements:

#1:

WITH data (f) AS (
  SELECT 'abc' UNION ALL
  SELECT 'def' UNION ALL
  SELECT 'abc'
)
SELECT f, ROW_NUMBER() OVER (ORDER BY f) FROM data GROUP BY f

#2:

WITH data (f) AS (
  SELECT 'abc' UNION ALL
  SELECT 'def' UNION ALL
  SELECT 'abc'
)
SELECT DISTINCT f, ROW_NUMBER() OVER (ORDER BY f) FROM data

(If you aren't, you can still see the results for yourself using Stack Exchange Data Explorer.)

Both statements are meant to return distinct items ranked. However, only the first statement (with GROUP BY) returns the results as expected, while the latter (with DISTINCT) returns all items. Obviously, ranking functions in SQL Server are evaluated after GROUP BY, but before DISTINCT The reason is, the SELECT clause in SQL Server is evaluated after GROUP BY, but before DISTINCT, which makes you prefer GROUP BY over DISTINCT in this case. (Thanks @ypercube for nudging me in the right direction.)

Upvotes: 7

Pranay Rana
Pranay Rana

Reputation: 176896

Something like this where I have not applied any aggregate function

Select City,State from geodata group by City,State

Upvotes: 4

Related Questions