GROUP BY

GROUP BY partitions the rows that survive WHERE into buckets keyed by the listed columns, then collapses each bucket into exactly one output row by folding its rows through the aggregate functions in the SELECT list. The engine does this either by hashing each row's group key into an in-memory hash table (one slot per distinct key, accumulators updated in place) or by sorting the rows on the key so equal keys land adjacent and a single linear pass emits a row per run. Everything else about the clause — the SQL-standard column rule, NULL handling, the MySQL footgun — falls out of that one idea: one output row per distinct group key.

The mechanism, traced

Take a small enrollments table and answer "how many students per course, and what's the average grade?"

SELECT course, COUNT(*) AS n, AVG(grade) AS avg_grade
FROM enrollments
GROUP BY course;

Input rows (already filtered by WHERE, in arrival order):

A hash aggregate keeps a running accumulator per key and updates it row by row — no full materialization, no sort:

1. Row (Asha, CS101, 90) → key CS101 not seen → create slot {count:1, sum:90}.
2. Row (Ben, MATH201, 70) → new key → slot {count:1, sum:70}.
3. Row (Cara, CS101, 80) → key exists → bump to {count:2, sum:170}.
4. Row (Dev, CS101, 85) → key exists → bump to {count:3, sum:255}.
5. Row (Eli, MATH201, 76) → key exists → bump to {count:2, sum:146}.

After the single pass, each slot emits one row, computing AVG = sum / count:

The output row order is not guaranteed — a hash aggregate emits groups in hash-bucket order, not sorted order. If you need ordered output, add ORDER BY course explicitly.

The column rule that trips everyone

The SQL standard is strict: once you GROUP BY, every column in the SELECT list (and HAVING, and ORDER BY) must be either a grouping column or wrapped in an aggregate. There is no other legal place for a value to come from — a group has many grade values but only one output row, so SELECT course, grade is ambiguous: which grade?

-- ❌ illegal: 'student' and 'grade' are neither grouped nor aggregated
SELECT course, student, grade
FROM enrollments
GROUP BY course;

-- ✅ legal: every non-grouped column is folded by an aggregate
SELECT course, COUNT(*) AS n, MAX(grade) AS top_grade
FROM enrollments
GROUP BY course;

Why the naive version is wrong: the grouped result has one row for CS101 covering three different students. student has no single value to put in that one cell, so the query is not answerable — it is a logic error, not a style nit.

Pitfalls

• MySQL's ONLY_FULL_GROUP_BY footgun. Historically MySQL silently allowed the illegal query above, returning an arbitrary grade from each group — no error, no warning, often the value from whichever row the engine happened to read first. Teams shipped reports that were quietly wrong. Since MySQL 5.7 the ONLY_FULL_GROUP_BY mode is on by default and rejects it with error 1055, matching the standard and PostgreSQL/SQL Server. If you inherit an old query that breaks after an upgrade, the right fix is to make intent explicit (ANY_VALUE(grade) if you truly don't care, or aggregate it), not to disable the mode.
• NULL is its own group. GROUP BY uses "not distinct" equality for keys, so all rows with a NULL grouping value collapse into a single NULL group — even though NULL = NULL is UNKNOWN everywhere else in SQL. A NULL course won't be dropped; it appears as one bucket with an empty/NULL key. Watch for it in COUNT(*) totals.
• COUNT(*) vs COUNT(col). COUNT(*) counts rows in the group; COUNT(grade) counts only rows where grade IS NOT NULL. AVG(grade) likewise ignores NULLs in both numerator and denominator — so an average can be over fewer rows than COUNT(*) suggests.
• WHERE filters rows, HAVING filters groups. WHERE runs before grouping and cannot see aggregates; HAVING runs after and is the only place to write HAVING COUNT(*) > 2. Putting an aggregate in WHERE is an error.
• Hash-aggregate output is unordered. Do not rely on rows coming back sorted just because you grouped — that only happens incidentally with a sort aggregate. Always add ORDER BY when order matters.

Takeaways

• GROUP BY = one output row per distinct key; the engine realizes it via a hash table (fast, unordered, memory ∝ groups) or a sort+scan (ordered, low memory). EXPLAIN tells you which.
• Every SELECT column must be a grouping column or inside an aggregate — there is physically only one row per group to hold a value.
• MySQL's ONLY_FULL_GROUP_BY enforces that rule; if a legacy query breaks, fix the query (aggregate or ANY_VALUE), don't disable the safety net.
• NULLs form one group; COUNT(col) and AVG(col) skip NULLs while COUNT(*) does not — these gaps quietly skew aggregates.

Sources: ISO/IEC 9075 SQL standard (grouped table semantics, §7.10); MySQL 8.0 Reference Manual — "MySQL Handling of GROUP BY" and the ONLY_FULL_GROUP_BY SQL mode; PostgreSQL documentation — "GROUP BY and HAVING Clauses" and the planner's HashAggregate vs GroupAggregate (sort) strategies; H. Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book (grouping and aggregation execution). Re-authored and deepened for this guide — added the hash-vs-sort mechanism, a step-by-step accumulator trace, the SQL-standard column rule with the MySQL ONLY_FULL_GROUP_BY footgun, and NULL-as-a-group handling; replaced the placeholder diagrams.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — GROUP BY

Why this concept exists (judgment layer)

GROUP BY is one output row per distinct key — hash or sort aggregate under the hood. ONLY_FULL_GROUP_BY and NULL-as-a-group are the production footguns that ship wrong reports.

Mental model (install this intuition)

After WHERE, partition by group keys; fold each partition with aggregates. Every SELECT expression must be a group key or aggregate. HashAggregate: O(rows) time, memory ~ distinct keys, unordered. GroupAggregate: sort then scan runs.

Worked example with numbers or traced steps

CS101: 90,80,85 → hash slot count=3 sum=255 avg=85
MATH201: 70,76 → count=2 sum=146 avg=73
Illegal: SELECT course, student GROUP BY course  -- which student?
NULL course rows → one NULL group (not dropped)
COUNT(*) = rows; COUNT(grade) skips NULL grades; AVG skips NULL too

When NOT to use / named alternative

Do not use GROUP BY to pick 'any' row per key without ANY_VALUE / DISTINCT ON / window ROW_NUMBER — that is the old MySQL silent wrong answer. Prefer window functions for top-N-per-group.

Failure mode & ops fingerprint

Fingerprint: MySQL without ONLY_FULL_GROUP_BY returns arbitrary student per course; hash aggregate spill to disk when group cardinality huge; ORDER BY assumed free after GROUP BY but hash path is unordered.

Hostile-panel drills (defend the decision)

Q1. Why is SELECT course, grade GROUP BY course illegal in standard SQL?
Model answer: A group has many grades but one output row; grade is neither a grouping column nor aggregated — ambiguous.

Q2. Hash vs sort aggregate trade-off?
Model answer: Hash: faster when groups fit memory, no order. Sort: lower memory, ordered by key, free if input already sorted/indexed.

Q3. WHERE vs HAVING?
Model answer: WHERE filters rows before grouping; HAVING filters groups after aggregates exist.