A Grouping Mistake
Why the rule matters.
Let's break the golden rule on purpose, so you recognise it. Many databases would refuse to run this query. SQLite is more relaxed. And that can be even more dangerous.

- Break the rule on purpose
- Many databases refuse
- SQLite runs it anyway
A misleading answer
We group by genre, but we also select the title, which isn't grouped, and isn't aggregated. Each genre has several titles. So which title should SQL show? Run it. SQLite just picks one, and doesn't tell you. It looks like a real answer, but it isn't. Postgres or SQL Server would give you an error instead.
SELECT genre, title, COUNT(*)
FROM books
GROUP BY genre
LIMIT 3;โโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโ โ genre โ title โ COUNT(*) โ โโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโค โ Business โ Numbers Don't Lie โ 3 โ โ Cooking โ The Quiet Kitchen โ 3 โ โ Fantasy โ Dragons of Ember Bay โ 3 โ โโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโ
Your turn
Your turn. For each staff role, show how many people have it, and the highest salary in that role. Sort so the most common role comes first. Pause and try. Here's a solution. Group by role, count star, and max of salary.
For each staff role: number of people and top salary
Show hint
GROUP BY role
Show solution
SELECT role,
COUNT(*) AS people,
MAX(salary) AS top_salary
FROM staff
GROUP BY role
ORDER BY people DESC;โโโโโโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโ โ role โ people โ top_salary โ โโโโโโโโโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโโค โ Bookseller โ 3 โ 32000 โ โ Web Support โ 1 โ 33000 โ โ Warehouse โ 1 โ 29000 โ โ Store Manager โ 1 โ 54000 โ โ Owner โ 1 โ 72000 โ โ Online Manager โ 1 โ 56000 โ โโโโโโโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโโ
Summary
If a column isn't grouped, aggregate it. Next, how to filter groups after they've been made. That's having.
Key takeaways- Ungrouped columns mislead
- Other databases give errors
- Group it, or aggregate it