Filtering groups: HAVING

Once you have a result per group (previous lesson), you often want to keep only the groups that satisfy a condition: departments with more than 2 people, projects with an average budget above 100k, users with at least 10 orders. To do this WHERE isn't enough.

Why WHERE isn't enough

WHERE filters the table rows, before aggregation. So it can't see the result of COUNT(*) or AVG(...): by the time WHERE runs, those values haven't been computed yet.

-- ERROR: COUNT(*) doesn't exist yet when WHERE is evaluated
SELECT department_id, COUNT(*) AS count
FROM   employees
WHERE  COUNT(*) > 2     -- ❌ doesn't work
GROUP BY department_id;

To filter after aggregation there's a dedicated clause: HAVING.

The syntax

SELECT grouping_column, AGGREGATE_FUNCTION(...)
FROM   table
GROUP BY grouping_column
HAVING   condition_on_aggregate;

-- Departments with more than 2 employees:
SELECT department_id, COUNT(*) AS count
FROM   employees
GROUP BY department_id
HAVING   COUNT(*) > 2;

HAVING is to GROUP BY what WHERE is to FROM: the same idea ("filter"), but applied after grouping, on the aggregate.

The logical order of clauses

To never confuse WHERE and HAVING again, remember the order in which SQL mentally executes them:

1. FROM / JOIN — builds the starting table by joining sources.
2. WHERE — drops the rows you don't care about.
3. GROUP BY — collects the remaining rows into groups.
4. HAVING — drops the groups you don't care about.
5. SELECT — computes the final columns (including aggregates).
6. ORDER BY / LIMIT — reorder and cut.

Important notes:

• in WHERE you can only use table columns;
• in HAVING you can use grouping columns and aggregates.

-- Classic combo: filter first, group, filter the groups, sort.
SELECT d.name, AVG(e.salary) AS average
FROM   employees   AS e
JOIN   departments AS d ON e.department_id = d.id
WHERE  e.hired_on >= '2018-01-01'   -- filter rows
GROUP BY d.name
HAVING   AVG(e.salary) > 35000      -- filter groups
ORDER BY average DESC;

Try it

Review exercise