GROUP BY and HAVING
Get a subtotal for every group, like the number of books in each genre.
Lesson 23 of 33 · about 15 minutes
The lesson
COUNT(*) gives one total. But what if you want a count for each genre? That's GROUP BY.
Picture tipping all the books onto a table and sorting them into piles, one pile per genre. Then you count each pile.
SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre;
You get one row per pile: Children 3, Fantasy 3, Fiction 8, and so on. Any aggregate works the same way: SUM(stock) per genre, AVG(price) per genre.
The golden rule: every column in SELECT should either be in GROUP BY or be inside an aggregate. A pile of Fiction books has one genre but many titles, so "the title of the Fiction pile" makes no sense.
Filter the piles with HAVING. WHERE can't use COUNT(*), because WHERE checks rows before the piles exist. HAVING checks the piles after:
SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre
HAVING COUNT(*) > 2;
The full order is now: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
- GROUP BY sorts rows into piles and gives one result row per pile.
- Columns in SELECT must be grouped or inside an aggregate.
- WHERE filters rows before grouping; HAVING filters groups after.
Practise
Quick check
1. How many rows does this return?
SELECT status, COUNT(*) FROM orders GROUP BY status;