HAVING - filtering groups

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.

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id
4HAVING 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.

1-- Categories whose average price exceeds 50
2SELECT kategoria_id, AVG(cena)
3FROM ksiazki
4GROUP BY kategoria_id
5HAVING AVG(cena) > 50;

1-- Countries from which we have at least 5 authors
2SELECT kraj, COUNT(*)
3FROM autorzy
4GROUP BY kraj
5HAVING COUNT(*) >= 5;

Order of clauses

Remember the order: GROUP BY always comes before HAVING:

1SELECT column, COUNT(*)
2FROM table
3GROUP BY column
4HAVING COUNT(*) > number;

HAVING is a sieve through which we pour the finished groups - only those large, expensive or numerous enough remain.