Filtering Groups

After you group rows with GROUP BY and apply aggregate functions, you often want to keep only some of the resulting groups. That is what HAVING is for. It filters groups the same way WHERE filters rows — but it runs after aggregation, not before.

The problem WHERE cannot solve

Suppose you want departments with more than 10 employees:

-- This fails — WHERE runs before grouping, so COUNT(*) doesn't exist yet
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 10
GROUP BY department;

The database raises an error because at the time WHERE is evaluated, the rows have not been grouped yet. You need a clause that runs after grouping.

HAVING filters groups

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

The database groups all rows by department, counts each group, then removes any group where the count is 10 or fewer. Only departments with more than 10 employees appear in the result.

Any aggregate function works in HAVING:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;

SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4.0 AND COUNT(*) >= 10;

The second query finds products that have at least 10 reviews and average at least 4 stars — a useful quality filter that pure WHERE cannot express.

WHERE and HAVING together

Both clauses can coexist. WHERE narrows the input rows first; HAVING filters the resulting groups:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 80000;

Execution order: filter employees hired since 2020, group by department, compute average salary per group, then keep only groups averaging above $80,000. Filtering in WHERE before grouping is also faster — fewer rows reach the grouping step.

The WHERE vs HAVING rule

A practical test: if your condition uses COUNT, SUM, AVG, MIN, or MAX, it belongs in HAVING. If it references a plain column value, it belongs in WHERE.