HAVING Clause

Here is a question beginners always ask: "If WHERE filters rows, how do I filter groups?" That is what HAVING is for.

WHERE filters rows before grouping. HAVING filters groups after grouping. Different job, different keyword.

HAVING vs WHERE

-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER grouping

-- Find cities where the average score is above 85
SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city
HAVING AVG(score) > 85;

You cannot use WHERE with aggregate functions. WHERE AVG(score) > 85 would give you an error. HAVING is the only way to filter on aggregated values.

WHERE and HAVING Together

You can use both in the same query. WHERE filters individual rows first, then the remaining rows are grouped, and HAVING filters the groups:

-- Only students above 21, grouped by city, 
-- show cities where average score is above 80
SELECT city, COUNT(*) AS count, AVG(score) AS avg_score
FROM students
WHERE age > 21
GROUP BY city
HAVING AVG(score) > 80;

Order of Execution

Understanding the order SQL processes things helps a lot:

1. FROM — get the data from the table
2. WHERE — filter rows
3. GROUP BY — group the filtered rows
4. HAVING — filter the groups
5. SELECT — pick the columns to show
6. ORDER BY — sort the final result

This mental model will save you so many bugs. I promise.