Filtering Groups with HAVING
WHERE for groups.
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.

- 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.
SELECT genre, SUM(stock) AS copies
FROM books
GROUP BY genre
HAVING SUM(stock) > 100
ORDER BY copies DESC;โโโโโโโโโโโโโฌโโโโโโโโโ โ 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.
SELECT author, COUNT(*) AS books
FROM books
GROUP BY author
HAVING COUNT(*) > 3;โโโโโโโโโโโฌโโโโโโโโ โ 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