Play free SQL arcade Daily trap SQL traps Blog Teachers Full game For teachers · 50 minutes
← Ready-to-teach lessons
GROUP BY and HAVING lesson
Counting, summing and filtering groups in 50 minutes, with the difference between WHERE and HAVING made clear. With a printable worksheet.
Goal
Every student can summarize a table by groups and knows when to filter rows and when to filter groups.
Lesson plan
0–5 min Introduce the topic on the board with one example query.
5–20 min Arcade , in pairs or alone. Arcade world 2: The Sorter, The Loot, The Podium and the boss, The Late Filter.
20–35 min Worksheet : six questions on the same tables.
35–45 min Arcade again: go back for three stars on the levels that were hardest.
45–50 min Class discussion with the questions below. Whoever beat the boss hands in their certificate code.
Materials
Mistakes you'll see
A column in SELECT that isn't in GROUP BY. DuckDB and PostgreSQL throw an error; MySQL, depending on its settings, may return an arbitrary value.A sum in the WHERE. WHERE runs before grouping: the total doesn't exist yet. see the explanation → Forgetting the NULL group. GROUP BY puts NULLs in a group of their own: the extra row surprises people.For the class discussion
Why does WHERE COUNT(*) > 2 fail while HAVING COUNT(*) > 2 works? If you filter with WHERE loot >= 500 and then group, why does a whole city disappear? What's the difference between COUNT(*) and COUNT(suspect_id) in this table? Evidence
When a student beats a boss, the arcade gives a certificate with a code. Ask for it as the submission and check it here: check codes →
Student worksheet
Ready to print, no answers.
Name: ______________________________ Date: ____________
GROUP BY and HAVING lesson
thefts id suspect_id city loot 1 2 Salt Harbor 1200 2 2 Salt Harbor 800 3 4 San Ciro 300 4 5 Salt Harbor 5000 5 8 Salt Harbor 450 6 8 Lumen Bay 950 7 8 San Ciro 150 8 1 Lumen Bay 2200 9 NULL Lumen Bay 700
How many thefts are there in total?
How many thefts there were in each city.
The total loot in each city.
The highest loot in each city.
The cities with more than 2 thefts.
Counting only thefts of 500 or more, the cities with at least 3.
casoabiertogame.com · SQL arcade and detective cases, free for the classroom
Want the answer key?
The worksheet answers and the solutions to all 28 cases aren't published, so students won't find them. Write to us from your institutional email and we'll send them, at no cost.
Request the answer key
The arcade, the lessons and the first two cases are free and need no account. For your group to have all 28 cases, the classroom license is free too: how to request it →