🧺GROUP BY
Buckets before folds
GROUP BY partitions the row multiset from FROM/WHERE/JOIN so each distinct combination of grouping keys becomes one group. Aggregates then run inside each bucket. In standard SQL, every column in SELECT must either appear in the GROUP BY list, be wrapped in an aggregate, or be functionally dependent on the group keys, engines with lax modes may allow “any value” picks; portable SQL should not rely on that.
Simple grouping
SELECT assay, COUNT(*) AS runs
FROM lab_runs
WHERE run_date >= DATE '2026-01-01'
GROUP BY assay
ORDER BY runs DESC;| assay | runs |
|---|---|
| ELISA | 420 |
| qPCR | 310 |
| NGS | 88 |
Multi-column keys
SELECT site, shift, COUNT(*) AS incidents
FROM safety_incidents
GROUP BY site, shift;| site | shift | incidents |
|---|---|---|
| BOS | DAY | 2 |
| BOS | NIGHT | 5 |
| SFO | DAY | 1 |
GROUP BY assay_id while SELECT shows assay_name
Without functional dependency rules, the engine may reject the query, or worse, in permissive modes return an arbitrary name per id during upgrades.
Group by the displayed key or aggregate the name
Add assay_name to GROUP BY, join to a dimension table guaranteed one row per id, or use MAX(assay_name) when names are truly 1:1 with ids.
📦Rollups
GROUPING SETS, CUBE, and ROLLUP add subtotal rows, great for pivot-style reports; test that consumers understand NULL vs “all” placeholders.
When cubes get large, materialized rollups in the warehouse often beat interactive mega-grouping on OLTP replicas.
🏗️Physical Aggregation Strategies
How the engine builds buckets in memory or on disk.
Hash vs Sort Aggregation
The optimizer chooses Hash Aggregation when it expects the distinct set of grouping keys to fit in memory. If the set is huge, it falls back to Sort Aggregation, which can spill sorted "runs" to disk and merge them.
| Strategy | Mechanical Path | Efficiency Note |
|---|---|---|
| Hash Aggregate | One-pass scan; build hash table of (Keys → Aggregates). | Fast O(N). Needs RAM proportional to # of groups. |
| Group Aggregate (Sort-based) | Sort input by keys, then walk and aggregate sequentially. | O(N log N). Low memory pressure; great for pre-sorted indexes. |
| Stream Aggregate | Aggregate data as it arrives from a sorted index. | Minimal CPU. Zero extra sorting required. |
The Sort Spill
If your `GROUP BY` cardinalty exceeds the server's memory grant, you will see "Sort Spills" or "Hash Spills" in the execution plan. This turns a memory-speed operation into a 100x slower disk-backed operation. Fix: Filter more aggressively before grouping, or increase database work memory.
TL;DR
GROUP BY partitions rows into buckets sharing the same key vector; aggregates then compute one value per bucket. Every non-aggregated column in SELECT must appear in GROUP BY (or be functionally dependent on the group in engines that allow it). Wrong grain, joining before grouping, duplicates rows and silently wrong totals.