GROUP BY

The last module collapsed a whole table to one number. GROUP BY does the same thing to each group separately: sort the rows into piles by some column, then run the aggregate once per pile.

SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status;

Three statuses, three rows out. The number of rows in the result is the number of distinct values in the grouping column — which is a useful thing to predict before you run anything.

Your task: from products, return each category and how many products are in it, as category and n.

ORDER BY still works, and it runs after the grouping — so it sorts the summary rows, not the original ones. That means you can order by an aggregate, which is how every "top categories" list is built.

You can sort by the alias you gave the aggregate. ORDER BY is one of the few places where an alias from the SELECT list is visible, because by then the SELECT list has been computed.

Your task: from events, return each device and how many events came from it as n, most events first.

Any aggregate works per group, not just COUNT. Swap in SUM and each pile is added up instead of counted.

Your task: from orders, return each user_id and the total amount they have spent as spent, biggest spender first.

A group can carry as many aggregates as you like, and they all describe the same pile. This is how a one-line-per-category summary gets built, and it costs no more than a single one because the rows are only read once.

Your task: from products, return each category, how many products it has as n, and the average price rounded to two decimals as avg_price — in that order, category name ascending.

List two columns and the group becomes the pair. Rows land in the same pile only when both values match, so the result has one row per combination that actually occurs.

Combinations that never happen simply do not appear. There are three plans and two activity states here, which is six possible pairs — and four of them exist.

Your task: from users, return plan, is_active and the count as n, ordered by plan then is_active.

WHERE filters rows, before they are sorted into groups. So a row removed by WHERE never reaches its group, and a group whose rows are all removed disappears from the result entirely.

That is the right tool when the condition is about a row. Filtering on the aggregate itself — "groups with more than three" — is a different clause, and it is the next module.

Your task: from orders, counting paid orders only, return each user_id and the count as n, ordered by user_id.

COUNT(DISTINCT ...) works per group too, and this is where it earns its keep: "how many different people did each thing" is a question about groups, and the row count answers it wrongly every time someone does the thing twice.

Your task: from events, return each event_name, how many events there were as n, and how many different users did it as users — ordered by event_name.

The rule for a grouped query is short: every column in the SELECT list must either be one you grouped by, or be wrapped in an aggregate. Anything else has one value per row and many rows per group, so there is no single answer for it.

Postgres refuses such a query outright, and names the column:

SELECT user_id, amount, COUNT(*)
FROM orders
GROUP BY user_id;

ERROR:  column "orders.amount" must appear in the
        GROUP BY clause or be used in an aggregate function

That error is a favour. Ten orders in a pile, ten different amounts — there is no correct value for amount to show, so Postgres declines to invent one. MySQL and SQLite will answer this query, picking a value from an arbitrary row without comment, which is far worse: it runs, the number looks plausible, nobody reviews it, and it can change when the data does.

Try it in the editor before you write the answer. Meeting the message once is what makes it readable the next time.

Your task: the fix is to say what you meant. From orders, return each user_id, the count as n, and the largest amount that user has spent in one order as biggest — ordered by user_id.

You can group by an expression rather than a stored column. The database computes it per row, then piles the rows up by the result — which is how you group by month, by first letter, or by a bucket you invent with the CASE from module 1-06.

ordered_at is a real DATE, so the year comes out of it with a function rather than by slicing characters: EXTRACT(YEAR FROM ordered_at) gives the number 2024. Module 2-04 covers the rest of the date toolkit.

Your task: from orders, return the year as yr and the number of orders in it as n, oldest year first.