SQL and MySQL course Β· Module 6: Aggregation and grouping

HAVING - filtering groups

2 min read Β·
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. 1. What is the HAVING clause used for?

  2. 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).

  1. QuestionWhat HAVING is for
  2. QuestionOrder of GROUP BY and HAVING
  3. Coding taskPopular categories
  4. Put in orderGrouping with a HAVING filter
  5. Coding taskExpensive categories
  6. QuestionHAVING and aggregate functions
  7. Coding taskFrequent author countries

Useful articles