GROUP BY: Collapsing Many Rows into One

Everyone can write GROUP BY, but nearly everyone has also been stopped by column "..." must appear in the GROUP BY clause, often without understanding “I’m just selecting a column, why not?” The answer, as in the earlier posts, hides in a simple model: what GROUP BY does is “collapse” each group’s many rows into one. Get that collapsing straight, and the bare-column error, the difference between WHERE and HAVING, even multi-level subtotals in one pass, all line up.

What GROUP BY does: collapsing

Think of GROUP BY customer as: first sort the rows into piles by customer, then squash each pile into one row. The aggregate functions (COUNT, SUM…) are the “compressor” — they compute one number from the many values a column has within a group:

This diagram directly explains that error: after collapsing, a group has only one row, but a non-key column like amount has several values within the group (100, 250), and SQL doesn’t know which to emit, so it won’t let you select it bare. You have exactly two options: put it in GROUP BY (making it part of the grouping), or squeeze it into one value with an aggregate (SUM(amount), MAX(amount)…).

This also connects to the first post‘s processing order: SELECT (⑤) runs after GROUP BY (③) — by the time SELECT computes columns, the rows have long been collapsed, so naturally only “grouping keys” and “aggregate results” are left to choose from.

Aggregate functions: squeezing many values into one number

The common ones are just these: COUNT, SUM, AVG, MIN, MAX. Two things to remember, both tied to the previous post’s NULL:

• Aggregates ignore NULL. AVG(score)’s denominator is “the number of non-NULL rows”, not NULL-as-zero — the average you compute may not be the one you think.
• COUNT(*) counts rows; COUNT(col) counts only non-NULLs. Subtract one from the other and you have the column’s NULL count.

One more detail you’ll meet even without GROUP BY: the moment an aggregate appears in SELECT, the whole table is treated as “one group”. So SELECT COUNT(*), MAX(amount) FROM orders returns one row — that’s just “collapsing without grouping”.

WHERE filters rows, HAVING filters groups

With the collapsing model, the division of labour between WHERE and HAVING follows naturally: WHERE filters row by row before collapsing; HAVING filters group by group after. So only HAVING can use an aggregate result as a condition — because that result exists only once collapsing is done:

SELECT customer, SUM(amount) AS total
FROM orders
WHERE amount > 0            -- ② before collapsing: remove invalid rows first
GROUP BY customer
HAVING SUM(amount) > 1000;  -- ④ after collapsing: keep only the "groups" over a thousand

WHERE SUM(amount) > 1000 errors outright — collapsing hasn’t happened, SUM doesn’t exist (the processing order again). And the reverse performance principle: rows you can cut with WHERE shouldn’t be left to HAVING; the earlier the data shrinks, the less there is to collapse and compute later.

Multi-level subtotals in one pass: ROLLUP

Finally, something very practical that many people don’t know. To get “detail + subtotal per region + grand total” you’d normally write three GROUP BY blocks and UNION them. With GROUP BY ROLLUP(...) it’s done in one pass — the extra subtotal/total rows use NULL to mark which level was rolled up:

No need to memorise the syntax; just remember “this exists”: when a report needs detail and per-level subtotals at once, ROLLUP / CUBE / GROUPING SETS compute it in one pass, no hand-built UNION.

Reflections

Think “collapsing” through, and bare columns stop being a mystery

When I was learning SQL, must appear in the GROUP BY clause was the error I hit most and most often “fixed” by throwing random columns into GROUP BY — which quietly wrecked the grouping logic. What actually solved it wasn’t memorising a rule but the picture: after grouping, each group is squashed into one row. Once that collapsing picture is in my head, I naturally know “what’s left to select” after collapsing — the grouping keys, and aggregates that squeeze many values into one. It’s the same epiphany as the processing order post: see the shape of the data at each stage, and the rules become self-evident.

Where aggregation goes wrong most: still NULL and fan-out

GROUP BY traps are rarely syntax; they’re mostly “wrong numbers, no error” — and the culprits are usually the two from the previous posts. One is NULL: AVG ignores NULL, COUNT(col) skips NULL, so the denominator you think you have isn’t the one you got. The other is JOIN fan-out: a join inflates the row count first, then SUM double counts. Now whenever I see “a join followed by GROUP BY ... SUM”, I stop and confirm the join didn’t duplicate fact rows — aggregation is the last step, and whatever earlier step dirtied the data, it will faithfully add the error up for you.

Most “write a few more queries” chores already have a move in SQL

ROLLUP taught me a habit: when I find myself UNIONing several near-identical queries that differ only in grouping level, stop and check whether there’s a ready-made move. SQL is an old, mature language, and the common needs — “detail plus subtotals”, “top N per group”, “contiguous ranges” — almost all have a tool designed for them (ROLLUP, window functions, GROUPING SETS). Rather than brute-forcing it with a pile of subqueries, slow and hard to read, spend ten minutes finding the right tool — which is what this series is about: don’t just brute-force SELECT; pick up what the language actually gives you.