GROUP BY & HAVING — Bucketing Rows

GROUP BY & HAVING — Bucketing Rows

The previous lesson's aggregates collapsed an entire table into one row. GROUP BY generalizes that: instead of one summary for the whole table, you get one summary per distinct value (or combination of values) of some column(s). HAVING then filters those group summaries — the aggregate-level counterpart to WHERE.

SELECT region, COUNT(*) FROM sales GROUP BY region
SELECT region, AVG(amount) FROM sales GROUP BY region HAVING COUNT(*) > 2

The algorithm: partition, then aggregate

1. Partition every row into buckets keyed by the tuple of its GROUP BY column values. Two rows land in the same bucket iff every grouping column matches.
2. Aggregate each bucket independently — run COUNT/SUM/AVG/MIN/MAX (from the previous lesson) over just that bucket's rows, not the whole table.
3. Apply HAVING as a post-aggregation filter: evaluate the HAVING condition (which itself is usually an aggregate expression, e.g. COUNT(*) > 2) against each group's computed aggregate, and drop groups that don't qualify.
4. Sort the surviving groups by their grouping column values (ascending) for deterministic output.

HAVING vs. WHERE — why you need both

WHERE filters individual rows before grouping — it can't reference an aggregate, because aggregates don't exist yet at that point. HAVING filters groups after aggregation, so it's the only clause that can express something like "only show regions with more than 2 sales" — a condition about the group as a whole, not about any single row. This split exists in every real SQL engine for the same reason: aggregation is a distinct execution phase, and predicates naturally split into "before" and "after" that phase.

Reusing your aggregate evaluator

The select-list here mixes two kinds of items — plain grouping-column names, and aggregate expressions:

SELECT region, COUNT(*), AVG(amount) FROM sales GROUP BY region

For each output row (one per surviving group), a plain column name just looks up that group's (constant, by definition of grouping) value; an aggregate expression gets evaluated against that bucket's row list using the same COUNT/SUM/AVG/MIN/MAX logic from the aggregates lesson — including the same NULL-exclusion rules (COUNT(col) skips NULL/empty cells, etc.).

Formatting and sort rules to nail

• Header is the select-list exactly as written, pipe-joined (region|COUNT(*)).
• Result rows sorted ascending by the grouping columns — numerically if the values parse as numbers, lexicographically otherwise.
• SUM prints as an int if every summed value was integral, else two-decimal float; AVG always prints with two decimals; MIN/MAX preserve the original string form of whichever value won (don't silently coerce a string column into a number).

HAVING condition evaluation

HAVING <agg> <op> <num> needs to: compute the named aggregate for the group, then compare it against the literal using the given operator (=, <, >, <=, >=, !=). Since group-level aggregate values can be numeric or NULL, decide early that a NULL aggregate value never satisfies a HAVING comparison (mirrors SQL's three-valued logic: comparisons against unknown are unknown, i.e. filtered out).

Edge cases to watch

• Multi-column GROUP BY (GROUP BY region, category) — the bucket key must be the combination, not either column alone.
• A grouping column value that's an empty string vs. NULL — don't conflate them when bucketing or when deciding what COUNT(col) should skip.
• HAVING referencing an aggregate that isn't in the SELECT list at all (e.g. filtering on COUNT(*) while only selecting region and AVG(amount)) — your engine still needs to compute it internally even though it isn't displayed.
• Numeric vs. string sort of the grouping key — "10" should sort after "9" numerically, but before it lexicographically; pick whichever the value parses as (numeric where parseable, else string) as the spec states.