Grouping by Two Columns
Piles inside piles.
Helen wants to compare years. In each year, how many orders were delivered, shipped, cancelled, or returned? That means grouping by the year, and by the status, at the same time.

- Compare the two years
- By year, and by status
- Two columns at once
Two grouping columns
List both after group by, with a comma. Now there's one pile for every combination of year and status. Run it. In twenty twenty five, one hundred orders were cancelled, compared with forty the year before. Cancellations grew faster than orders. That's worth investigating.
SELECT strftime('%Y', order_date) AS year,
status,
COUNT(*) AS orders
FROM orders
GROUP BY year, status
ORDER BY year, status;โโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโ โ year โ status โ orders โ โโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโค โ 2024 โ cancelled โ 40 โ โ 2024 โ delivered โ 442 โ โ 2024 โ returned โ 22 โ โ 2024 โ shipped โ 55 โ โ 2025 โ cancelled โ 100 โ โ 2025 โ delivered โ 823 โ โ 2025 โ returned โ 41 โ โ 2025 โ shipped โ 75 โ โโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโ
The golden rule
Here's the golden rule of grouping. Every column in your select must be either in the group by, or inside an aggregate function. Year and status are in the group by. Count is an aggregate. So this query follows the rule.
Summary
You can group by several columns, and you know the golden rule. Next, what happens when you break it.
Key takeaways- GROUP BY a, b
- One group per combination
- Group it, or aggregate it