SQL HAVING: filter groups

After a GROUP BY, you sometimes want to keep only some groups: the cities with more than 10 customers, the categories whose average price exceeds €20, the customers who placed at least 5 orders. WHERE cannot do this, because it acts before the grouping. You need HAVING.

Syntax

SELECT column, FUNCTION(column)
FROM table
WHERE condition_on_rows
GROUP BY column
HAVING condition_on_groups;

How it works

You can use both in the same query: WHERE first removes rows, GROUP BY builds the groups from the remaining ones, then HAVING removes groups.

When a condition is on a plain column (not on an aggregate), put it in the WHERE instead: the database then works on fewer rows.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT ville, COUNT(*) AS nb_clients
FROM clients
GROUP BY ville
HAVING COUNT(*) > 10;

SELECT categorie, ROUND(AVG(prix), 2) AS prix_moyen
FROM produits
GROUP BY categorie
HAVING AVG(prix) > 30;

SELECT client_id, COUNT(*) AS livrees
FROM commandes
WHERE statut = 'livrée'
GROUP BY client_id
HAVING COUNT(*) >= 6
ORDER BY livrees DESC;

Common mistakes

SELECT ville, COUNT(*) FROM clients WHERE COUNT(*) > 10 GROUP BY ville;

SELECT ville, COUNT(*) FROM clients GROUP BY ville HAVING COUNT(*) > 10;

SELECT ville, COUNT(*) FROM clients HAVING COUNT(*) > 10 GROUP BY ville;

SELECT ville, COUNT(*) FROM clients GROUP BY ville HAVING COUNT(*) > 10;

SELECT ville, COUNT(*) FROM clients GROUP BY ville HAVING ville <> 'Paris';

SELECT ville, COUNT(*) FROM clients WHERE ville <> 'Paris' GROUP BY ville;

Exercises

Try writing the query yourself before opening the solution.

SELECT departement, ROUND(AVG(salaire)) AS moyenne FROM employes GROUP BY departement HAVING AVG(salaire) > 4000;

SELECT commande_id, COUNT(*) AS nb_lignes FROM lignes_commande GROUP BY commande_id HAVING COUNT(*) >= 4 LIMIT 10;

Frequently asked questions