Filtering Groups

HAVING is a small keyword with one job: filtering groups after aggregation runs. Once you understand why WHERE cannot do this, the rest follows naturally.

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. At the point WHERE is evaluated, the rows have not been grouped yet - COUNT(*) does not exist as a value. You need a clause that waits until after grouping finishes.

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 discards any group where the count is 10 or fewer. Only departments with more than 10 employees survive.

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 is a good real-world pattern: products with at least 10 reviews and an average rating of at least 4 stars. That kind of quality filter is impossible with WHERE alone.

WHERE and HAVING together

Both clauses can coexist in the same query. WHERE narrows the rows going in; HAVING filters the groups coming out:

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

Read it in execution order: WHERE trims to post-2020 hires, then GROUP BY forms department buckets, then HAVING drops the low-salary ones. Filtering early in WHERE also makes the query faster - fewer rows reach the grouping step at all.

The WHERE vs HAVING rule

A quick rule of thumb: if your condition references a COUNT, SUM, AVG, MIN, or MAX, it belongs in HAVING. If it references a plain column value, it belongs in WHERE. When in doubt, ask yourself when the value you are filtering on actually exists.