HAVING — Filtering Groups

WHERE throws away rows before grouping. HAVING throws away groups after.

SQL
SELECT dept_id, COUNT(*) AS staff
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 3
ORDER BY staff DESC;

All four departments are grouped; only those with three or more people survive. WHERE COUNT(*) >= 3 would be an error — at WHERE time no group exists yet, so there is nothing to count.

Where each one sits

FROM      → assemble rows
WHERE     → drop rows            ← individual rows
GROUP BY  → form groups
HAVING    → drop groups          ← whole buckets
SELECT    → compute columns
ORDER BY  → sort

Using both, which is the normal case

SQL
SELECT dept_id,
       COUNT(*)              AS active_staff,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE active = 1
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

Read it in execution order: keep active employees, group them by department, keep departments with at least two of them, then compute and sort.

The distinction that changes the answer

These two queries look almost identical and do not mean the same thing:

SQL
SELECT dept_id, ROUND(AVG(salary), 2) AS avg_of_high_earners
FROM employees
WHERE salary > 70000
GROUP BY dept_id
ORDER BY dept_id;

SQL
SELECT dept_id, ROUND(AVG(salary), 2) AS dept_avg
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 70000
ORDER BY dept_id;

The first averages only the salaries above 70,000, department by department. The second averages everyone and then keeps departments whose average clears 70,000. Different questions, different numbers, and both are valid SQL — so nothing tells you which one you meant except reading it carefully.

The rule of thumb: if the condition is about an individual row, it belongs in WHERE. If it is about the group as a whole, it belongs in HAVING.

HAVING without GROUP BY

Legal, and occasionally useful: the whole table is treated as one group.

SQL
SELECT COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
FROM orders
HAVING SUM(amount) > 100;

The row appears because total revenue clears 100. Had it not, you would get zero rows — which is a neat way to say "give me this summary only if it matters", and a common trick in alerting queries.

Put row conditions in WHERE, not HAVING

Most row-level conditions can be written in HAVING when the column is in the GROUP BY, and it is a bad habit:

SQL
SELECT dept_id, COUNT(*) AS staff
FROM employees
GROUP BY dept_id
HAVING dept_id = 1;

This works and returns the right answer, but it groups all ten employees and then discards three of the four buckets. WHERE dept_id = 1 groups four rows and no more. On a table with millions of rows that is the difference between a fast query and a slow one, and it is a common review comment.

Aliases in HAVING

Standard SQL does not let HAVING use a SELECT alias — HAVING staff >= 3 fails on PostgreSQL and Oracle, because HAVING runs before SELECT. MySQL and SQLite permit it as an extension. Repeat the aggregate to stay portable.

Common mistakes

An aggregate in WHERE — the error that sends people here.
A row condition in HAVING — correct but wasteful, and it hides intent.
Confusing "average of the filtered rows" with "filter on the average" — the two queries above.
Relying on aliases in HAVING — works on MySQL, fails on Postgres.

Interview question

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot see aggregates; HAVING filters groups after grouping and is where aggregate conditions go. The follow-up worth preparing: give an example where moving a condition between them changes the result — the two queries above are exactly that.

Check yourself

Find customers who have placed more than one order.
Rewrite HAVING dept_id = 2 as a WHERE and say why it is better.
Why does HAVING staff >= 3 fail on PostgreSQL when staff is a SELECT alias?