AI Hints
Homeโ€บSQL Tutorialโ€บFiltering Groups
SQL ยท Lesson 39 of 126

Filtering Groups with HAVING

WHERE for groups.

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

Helen asks, which genres have more than one hundred copies on the shelves? That's a test on the total of each group. But where can't do this. Where looks at single rows, before any groups exist. We need a test that runs after grouping. That's having.

Filtering Groups with HAVING
From the video: Filtering Groups with HAVING
  • Genres with over 100 copies
  • A test on each group's total
  • WHERE can't see groups

HAVING

Having goes right after group by. It works like where, but its test can use aggregate functions, like sum of stock. Run it. Four genres have more than one hundred copies. Thrillers lead, with one hundred and sixty.

query.sqlsql
SELECT genre, SUM(stock) AS copies
FROM books
GROUP BY genre
HAVING SUM(stock) > 100
ORDER BY copies DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   genre   โ”‚ copies โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Thriller  โ”‚ 160    โ”‚
โ”‚ Romance   โ”‚ 136    โ”‚
โ”‚ Self-help โ”‚ 114    โ”‚
โ”‚ Mystery   โ”‚ 105    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Prolific authors

Which authors have written more than three of Helen's books? Group by author, count, and keep groups where the count is more than three. Run it. Only Ada Obi, with four books.

query.sqlsql
SELECT author, COUNT(*) AS books
FROM books
GROUP BY author
HAVING COUNT(*) > 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ author  โ”‚ books โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Ada Obi โ”‚ 4     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Having filters groups, using aggregates. Next, using where and having together, and how to choose between them.

Key takeaways
  • HAVING filters groups
  • It can use aggregates
  • It comes after GROUP BY
Next up: WHERE versus HAVING โ€” before and after grouping