AI Hints
Homeโ€บSQL Tutorialโ€บGrouping Rows
SQL ยท Lesson 36 of 126

More Grouping

Several numbers per group.

โฑ 2 min readโ–ถ Video at 51:08๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

More Grouping
From the video: More Grouping
  • 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.

query.sqlsql
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
SELECT genre,
       COUNT(*)             AS books,
       ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
ORDER BY avg_price DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Grouping by Two Columns โ€” piles inside piles