SQL and MySQL course Β· Module 6: Aggregation and grouping
HAVING - filtering groups
In this lesson3
We can already count the books in each category. But what if we are interested in only the popular categories - those with more than three books? We cannot use WHERE - you will learn why in a moment. The HAVING clause is what filters groups.
What is HAVING?
HAVING filters whole groups based on the result of an aggregate function - already after GROUP BY has run.
SELECT kategoria_id, COUNT(*)
FROM ksiazki
GROUP BY kategoria_id
HAVING COUNT(*) > 3;This means: "Show the categories, but only those that have more than 3 books". First MySQL split the books into groups, counted each one, and then discarded the groups that did not meet the condition.
HAVING works on aggregates
The most important feature of HAVING: it can refer to aggregate functions, which WHERE cannot use.
-- Categories whose average price exceeds 50
SELECT kategoria_id, AVG(cena)
FROM ksiazki
GROUP BY kategoria_id
HAVING AVG(cena) > 50;-- Countries from which we have at least 5 authors
SELECT kraj, COUNT(*)
FROM autorzy
GROUP BY kraj
HAVING COUNT(*) >= 5;Order of clauses
Remember the order: GROUP BY always comes before HAVING:
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > number;HAVING is a sieve through which we pour the finished groups - only those large, expensive or numerous enough remain.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is the HAVING clause used for?
2. In what order must the GROUP BY and HAVING clauses appear?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that selects kategoria_id and COUNT(*) from the ksiazki table, groups by kategoria_id, and shows only groups with a book count greater than 3 (HAVING).
- Vertical ordering
Arrange the clauses of a grouping query with a group filter in the correct order (top to bottom):
- Code editor
Write a query that selects kategoria_id and AVG(cena) from the ksiazki table, groups by kategoria_id, and keeps only the groups where AVG(cena) is greater than 50.
- Code editor
Write a query that selects kraj and COUNT(*) from the autorzy table, groups by kraj, and keeps only the groups with an author count greater than or equal to 5 (HAVING COUNT(*) >= 5).