Must group to Aggregate?

Question

Is it a requirement that you have to group by something in your select if aggregating such as using SUM? That you must have a group by clause?

I suppose you could get weird with sub-queries or something to prove a point - but typically yes. (unless of course you want the sum of every single record returned by the query) — RThomas, Jul 10 '12 at 16:16

score 6 · Answer 1 · answered Jul 10 '12 at 16:28

6

No it is not a requirement.

Aggregates without a GROUP BY are Scalar aggregates and always return exactly one row.

Example

SELECT SUM(high)
FROM master..spt_values
WHERE 1 = 0

Aggregates with a GROUP BY are Vector aggregates and return zero or one row per group.

Example

SELECT [type], SUM(high)
FROM master..spt_values
WHERE 1 = 0
GROUP BY [type]

answered Jul 10 '12 at 16:28

Martin Smith

+1 Actually, aggregating always implies grouping. It's just that if `GROUP BY` is omitted (which it may indeed), then `GROUP BY ()` is implied. – Andriy M Jul 11 '12 at 08:46
1

@AndriyM - Interesting point. `SELECT SUM(high) FROM master..spt_values WHERE 1 = 0 GROUP BY ()` returns no rows. Without the `GROUP BY()` returns one row so they are not equivalent. – Martin Smith Jul 11 '12 at 08:49
Something to ponder over. It does work without `WHERE 1=0`, though. I wonder if this has ever been asked about... – Andriy M Jul 11 '12 at 08:56
Well, so far my conclusion is that actual (or, should I say, *explicit*) `GROUP BY ()` doesn't return rows on empty datasets. In all other cases the results with `GROUP BY ()` seem to be exactly the same as those produced by identical queries with no `GROUP BY` clause. Even if it eventually proves the only difference, it's sufficient enough to take my words (expressed in the first comment) back. – Andriy M Jul 11 '12 at 09:19

1 Answers1