GROUP BY

This is the lesson where SQL starts paying you back. Everything so far has been about rearranging rows you already had. GROUP BY produces a table that does not exist in the file: one row per category, with your own numbers on it. It is the query behind essentially every summary table, every bar chart and every "by region" slide anyone has ever asked you for.

The mental model is worth getting right the first time. The engine sorts every row into buckets, one bucket per distinct value of the grouping column. Then it runs your aggregate separately inside each bucket. Then it emits one row per bucket. Four regions in, four rows out.

The rule that explains every error message

Every column in the select list must either be in the GROUP BY, or be wrapped in an aggregate. No exceptions. If you have grouped by region and you also ask for product, the engine has a genuine problem: the West bucket holds eight different products and it can only put one value in the cell. So it refuses, and the message says something about product not appearing in the GROUP BY clause.

When you hit that, the question to ask yourself is which of two things you actually wanted. If you want product broken out too, add it to the GROUP BY and you get one row per region and product pair, which is lesson 9. If you wanted one representative product, say which one: MAX(product) or string_agg(DISTINCT product, ', ') are both honest answers where a bare column name is not.

MySQL historically let the bare column through and picked a row at random. It caused enough silently wrong reports that MySQL turned the strict behavior on by default years ago. If you learned SQL on an old MySQL and this rule feels new, that is why.

Sorting the summary

A grouped result has no more inherent order than any other result, so put an ORDER BY on it. The conventional choice is the aggregate, descending, so the biggest category is first:

SELECT region, SUM(revenue) AS total
FROM csv
GROUP BY region
ORDER BY total DESC

Notice that the ORDER BY refers to the alias total rather than repeating SUM(revenue). That is allowed everywhere, because ORDER BY is the last thing to run and the aliases exist by then. It is one of the few places where SQL's clause ordering works in your favour.

Sorting alphabetically by the category instead is right when the categories have a natural order, such as months, or when people will scan the table looking for a specific one. Sorting by size is right when the question is "which is biggest". Choose deliberately.

Where the numbers should be checked

A grouped total is easy to get subtly wrong, and there is one check that catches most of it: the group totals should add up to the ungrouped total. Run SELECT SUM(revenue) FROM csv and then add up the four regional totals by eye. If they disagree, something is being dropped, and on real data that something is usually rows where the grouping column is null.

GROUP BY does not discard nulls. It gives them their own bucket, which appears as a row with an empty category and a real number beside it. That row is easy to miss at the bottom of a sorted list and it is frequently the most interesting row in the result: it is the orders with no region, the tickets with no owner, the payments with no invoice.

Grouping by an expression

You are not limited to grouping by columns that exist. Any expression works, including one you have aliased:

SELECT date_trunc('month', date) AS month, SUM(revenue) AS total
FROM csv
GROUP BY month
ORDER BY month

A hundred daily rows collapse into six monthly ones. Grouping by an alias like this is a DuckDB and Postgres convenience; strict standard SQL wants the whole expression repeated in the GROUP BY. Both forms work here, and lesson 10 is entirely about the date functions that make this useful.

The same trick with a CASE expression lets you group by a category you invent on the spot, such as small, medium and large orders. That is lesson 11, and together the two are most of what a pivot table does.

What to look up next

Look up GROUPING SETS, ROLLUP and CUBE. They let one query return the per-region totals and the grand total together, correctly labelled, which is exactly the thing everyone otherwise builds by running two queries and pasting them into a spreadsheet.