More Grouping
Several numbers per group.
Once you understand the piles idea, you can ask lots of questions. And each pile can get as many aggregate functions as you like. Count, sum, average, all at once.

- The piles idea
- Lots of questions
- Several aggregates per pile
Orders by status
How many orders are in each status? Group by status, and count. Run it. One thousand two hundred and sixty five were delivered. And one hundred and forty were cancelled. That's almost one in eleven, which Helen thinks is too many.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;โโโโโโโโโโโโโฌโโโโโโโโโ โ status โ orders โ โโโโโโโโโโโโโผโโโโโโโโโค โ delivered โ 1265 โ โ cancelled โ 140 โ โ shipped โ 130 โ โ returned โ 63 โ โโโโโโโโโโโโโดโโโโโโโโโ
Several numbers per genre
Now for each genre, the number of books, and the average price. Both aggregates run on each genre's pile. Run it. Technology books are the most expensive on average, at thirty two thirty two, and romance the cheapest.
SELECT genre,
COUNT(*) AS books,
ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY avg_price DESC;โโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโ โ genre โ books โ avg_price โ โโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโค โ Technology โ 3 โ 32.32 โ โ Cooking โ 3 โ 24.82 โ โ Business โ 3 โ 21.99 โ โ History โ 3 โ 20.66 โ โ Fantasy โ 3 โ 17.82 โ โ Self-help โ 3 โ 14.49 โ โ Thriller โ 4 โ 13.37 โ โ Mystery โ 4 โ 13.12 โ โ Romance โ 4 โ 10.24 โ โโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโ
Busiest months
Group by works with where, and with any expression, like the month. Here we take only twenty twenty five, group by month, and keep the three busiest. Run it. August was the busiest month of the year, with one hundred and twenty orders.
SELECT strftime('%m', order_date) AS month,
COUNT(*) AS orders
FROM orders
WHERE strftime('%Y', order_date) = '2025'
GROUP BY month
ORDER BY orders DESC
LIMIT 3;โโโโโโโโโฌโโโโโโโโโ โ month โ orders โ โโโโโโโโโผโโโโโโโโโค โ 08 โ 120 โ โ 12 โ 104 โ โ 05 โ 103 โ โโโโโโโโโดโโโโโโโโโ
Summary
Each group can have several aggregates, and you can group by an expression like the month. Next, grouping by two things at once.
Key takeaways- Several aggregates per group
- WHERE filters before grouping
- Group by an expression