Filter Rows and Groups With WHERE and HAVING

The order the database works in

A grouped query is processed in roughly this order: FROM and the joins, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. That order explains every rule about the two clauses.

WHERE sees one row at a time. It can test hire_date or salary, but it can't test a count, because no groups exist yet. HAVING sees one group at a time, after the aggregates are computed, so it's the place for COUNT(*), SUM(...) or AVG(...).

The query that fails

The most common mistake is putting the aggregate in WHERE:

SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) >= 4
GROUP BY department_id;

SQLite stops with misuse of aggregate: COUNT(), and other databases give a similar error. The fix is to move that condition into HAVING.

The query that runs but answers a different question

The quieter mistake is dropping the row filter. Without the WHERE, the count covers every employee ever hired:

SELECT d.name AS department, COUNT(*) AS new_hires, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
HAVING COUNT(*) >= 4
ORDER BY new_hires DESC, department;

That returns all five departments, from Engineering with 13 people down to HR with 4. No error, just the wrong answer. With the WHERE in place, only 19 employees hired from 2020 onward are counted. Engineering has 6 of them, Sales and Marketing have 4 each, Finance has 3 and HR has 2, so the right result is three departments.

This is why the two clauses aren't interchangeable. The same COUNT(*) >= 4 gives a different result depending on which rows reach GROUP BY.

Using both in one query

Most real questions need both. Here's the same report with a different group filter, keeping departments where recent hires average more than 100000:

SELECT d.name AS department, COUNT(*) AS new_hires, ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.hire_date >= '2020-01-01'
GROUP BY d.name
HAVING AVG(e.salary) > 100000;

Engineering and Sales stay, both at 107500. Compare that with putting the salary test in WHERE:

SELECT d.name AS department, COUNT(*) AS new_hires
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.hire_date >= '2020-01-01' AND e.salary > 100000
GROUP BY d.name;

Now the question is "how many recent hires earn over 100000 in each department", and four departments come back (Engineering 4, Sales 3, Finance 1, Marketing 1). A salary test on single rows and an average test on groups are different questions.

When either clause works

A condition on a grouping column can go in either place. These two return the same four departments without HR:

SELECT d.name AS department, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name <> 'HR'
GROUP BY d.name;

SELECT d.name AS department, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
HAVING d.name <> 'HR';

Prefer WHERE here. It removes rows before the grouping work, and it tells the reader the condition is about rows, not totals. Many optimizers will move such a HAVING condition into WHERE for you, but you shouldn't count on it.

What to say in the interview

Give the one-line rule first: WHERE filters rows before grouping, HAVING filters groups after it. Then mention the execution order, and say that aggregates can only be filtered in HAVING. If you have time, point out that non-aggregate conditions belong in WHERE even when HAVING would accept them.

For more practice, try Departments With More Than 5 Employees, the Conditional Count With SUM and CASE question, or the GROUP BY interview questions.