Chapter 2 · 8 min
Grouping and counting rows
Aggregation questions are where interviewers find out whether you understand
what a query returns, not just whether it runs. GROUP BY collapses rows into
one output row per distinct combination of the grouped columns, and the
aggregates you select are computed inside those groups.
SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;
Read that query as a sequence of decisions rather than a sentence: GROUP BY
decides what a “row” means in the output, and the aggregate decides what is
summarised per group. The result has one row per status, and count(*) counts
the rows in each group — not the rows in the table.
Two rules that interviews test repeatedly:
- Every non-aggregated column in
SELECTmust be grouped. Selectingcustomer_idalongsidestatuswithout grouping both makes the output ambiguous, and Postgres rejects it rather than guessing. - Filtering happens in two different places.
WHEREremoves rows before grouping;HAVINGremoves groups after aggregation. Asking for “customers with more than three paid orders” needs the count to exist first, so the condition belongs inHAVING, notWHERE.
SELECT customer_id, count(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING count(*) > 3;
The order of those clauses is not cosmetic. A WHERE clause cannot see an
aggregate, because aggregation has not happened yet; a HAVING clause that
filters on a plain column would be doing work WHERE could have done earlier
and more cheaply.
One more habit worth building now: name the aggregate. A count returned as
count is technically fine, but interview questions usually specify the output
column name, and an unexpected name reads as a wrong answer even when the
numbers are right.