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:

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.