How databases implement GROUP BY aggregation

WHY IT EXISTS GROUP BY collapses many input rows into one row per distinct grouping-key value, computing an aggregate per group. The engine needs a way to route each row to its group and accumulate the aggregate efficiently.

THE TWO MAIN ALGORITHMS Hash aggregation. The engine builds a hash table whose key is the grouping columns and whose value is the running aggregate state for that group. As each input row arrives, it hashes the key, finds or creates the group's entry, and updates the state; for COUNT(*) it increments the counter, for SUM it adds, for AVG it tracks sum and count. After consuming all input it emits one row per hash-table entry. It requires no input ordering and is typically fastest, but memory grows with the number of distinct groups, so when the table exceeds the memory budget the engine spills partitions to disk and processes them in passes.

Sort-based aggregation. The engine sorts the input on the grouping key, then scans the sorted stream once; because equal keys are now adjacent, it aggregates a run of identical keys and emits a result when the key changes. It uses minimal memory beyond the sort and yields output already sorted by the group key, which helps if a downstream ORDER BY matches, but it pays the sort cost.

HOW THE OPTIMIZER CHOOSES It weighs estimated group cardinality, available memory, and whether the input is already sorted (for example by an index), picking hash for many small groups in memory and sort when input is pre-ordered or groups are huge.

LIKELY FOLLOW-UPS How does hash aggregation spill to disk, when does an index make sort aggregation free, and how is partial aggregation used in parallel and distributed plans.

ONE CONCRETE EXAMPLE For SELECT country, COUNT(*) FROM users GROUP BY country, hash aggregation keeps a small table of country to counter, incrementing the right counter per row, then outputs each country with its count in a single pass over the table.