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

A Grouping Mistake

Why the rule matters.

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

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.

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

query.sqlsql
SELECT genre, title, COUNT(*)
FROM books
GROUP BY genre
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

โœ๏ธ Exercise: Your turn

For each staff role: number of people and top salary

Show hint

GROUP BY role

Show solution
solution.sqlsql
SELECT role,
       COUNT(*)    AS people,
       MAX(salary) AS top_salary
FROM staff
GROUP BY role
ORDER BY people DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚      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
Next up: Filtering Groups with HAVING โ€” WHERE for groups