GROUP BY & HAVING — Bucketing Rows

Aggregate functions collapse rows. GROUP BY says first split rows into buckets, then aggregate inside each bucket.

SELECT region, SUM(amount) AS revenue
FROM sales
GROUP BY region;

This gives you one row per region, with revenue summed within each. The non-aggregate column (region) must appear in GROUP BY — otherwise its value would be ambiguous within a group.

Logical Execution Order

SQL syntax order does NOT match execution order. The logical sequence is:

1. FROM — load tables
2. WHERE — filter individual rows ← happens BEFORE grouping
3. GROUP BY — bucket rows
4. HAVING — filter buckets ← happens AFTER aggregation
5. SELECT — compute output columns (including aggregates)
6. ORDER BY — sort
7. LIMIT — top-K

This is why WHERE COUNT(*) > 10 is invalid — at the WHERE step COUNT(*) doesn't exist yet — and you must use HAVING COUNT(*) > 10 instead.

Hash Aggregation

The most common execution strategy is hash aggregation:

state := empty hash map  // group_key → running aggregate state
for row in input:
    key  := row[group_cols]
    s    := state.getOrInit(key)
    s.count += 1
    s.sum   += row.amount       // for SUM(amount)
    s.min   := min(s.min, row.amount)
    ...
for (key, s) in state:
    emit (key, s.count, s.sum, ...)

Each aggregate maintains a tiny running state — COUNT is just an integer, SUM is a running sum, AVG keeps (sum, count) and divides at the end, MIN/MAX hold the current extreme. Memory is O(distinct group keys), not O(rows).

Sort Aggregation

When the input is already sorted on the grouping columns (often because an index supplies it that way), the engine can stream through it without a hash table:

prev_key := nil; s := empty
for row in input:
    if row.key != prev_key:
        if prev_key != nil: emit (prev_key, s)
        s := empty
        prev_key := row.key
    update(s, row)
emit (prev_key, s)

This uses constant memory regardless of group count — ideal for very high cardinality grouping. Postgres uses sort aggregation when work_mem can't fit the hash table.

HAVING

HAVING is just a filter that runs after aggregation. Internally it's the same predicate evaluator as WHERE — it just operates on the post-aggregation tuples (region, sum_amount) instead of raw rows.

GROUPING SETS, CUBE, ROLLUP

Real engines extend GROUP BY with ROLLUP(a, b) — equivalent to grouping by (a,b), (a), and () — and CUBE(a, b) — all 4 subsets. Internally these expand to multiple GROUP BY passes whose results are unioned. We won't implement them here but they fall out naturally from the same hash-aggregation primitive.