SQL90 min total · 18 parts
SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries
Part 9 of 18 · ~3 min
GROUP BY and Aggregate Functions in Depth
Mechanically, GROUP BY scans through the rows, buckets them by whatever value sits in the named column, and hands back exactly one output row per bucket — three departments in, three rows out, no matter how many employees fed each bucket. That collapsing is precisely why every remaining column in your SELECT list needs a plan for what to do with the (potentially many) rows that got folded together: name it in GROUP BY too, or wrap it in something like COUNT() or AVG() that knows how to turn many values into one. Skip that, and different engines react in genuinely different ways. Postgres and SQL Server both treat it as a hard error and refuse to run the query at all. MySQL, for a long stretch of its history, just shrugged and returned whichever row's value happened to be lying around first. SQLite — the engine powering every query in this reference, including the code lab you're pasting into — takes the exact same permissive stance MySQL used to, which is worth confirming for yourself before it confirms itself to you the hard way:
SELECT dept, name, COUNT(*) FROM Employees GROUP BY dept;
-- HR | Eve | 1
-- IT | Carol | 2 ← 'Carol' — SQLite just picked one of the two names, arbitrarily
-- Sales | Alice | 2 ← same thing here
That runs without a single warning in the code lab, and name in the output is genuinely meaningless — SQLite picked a row's name for IT, not the "first" or "highest-paid" one by any rule you can rely on. If you paste something like this into the code lab and it works, that is not the database telling you the query is correct. It's the database being permissive in a way Postgres and SQL Server would have caught for you.
With that guardrail understood, here's the report's actual per-department summary, built the way it should be:
SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary, MAX(salary) AS top_salary
FROM Employees
GROUP BY dept;
Here's the reference worth bookmarking — what a report-writer actually needs to know about each one before trusting it near a nullable column:
| Function | Answers | Where NULL fits in |
|---|---|---|
COUNT(*) | "How many rows?" | Doesn't inspect any column, so NULLs are irrelevant to it entirely |
COUNT(column) | "How many rows have something here?" | A NULL cell simply isn't tallied |
SUM(column) | "What's the total?" | NULL cells contribute nothing to the running total — and a group that's NULL all the way through sums to NULL, never 0 |
AVG(column) | "What's the mean?" | Same exclusion as SUM, which means the denominator is the count of populated cells, not the row count |
MIN(column) / MAX(column) | "What's the extreme?" | NULL cells are invisible to the comparison, as if those rows weren't there |
Memorize the SUM row above the rest, because it's the one that ambushes people downstream, once its result gets compared against something with > or <. Picture a department where every single salary was somehow NULL — HAVING SUM(salary) > 0 wouldn't flag it as suspicious and wouldn't quietly count it as 0 either. It would just vanish from the report, because NULL > 0 resolves to UNKNOWN, and HAVING throws away UNKNOWN rows exactly as readily as FALSE ones. Same three-valued logic from three chapters back, wearing a different costume.