HAVING and the Order SQL Actually Runs In

You can now group and aggregate. The next question is inevitable: "show me only the departments with more than one person", a filter on an aggregated value. That needs HAVING, not WHERE. Understanding why means understanding the order SQL really executes in.

In one line

WHERE filters rows before grouping; HAVING filters groups after aggregating. You can't put COUNT(*) in WHERE because the count doesn't exist yet at that stage.

The order SQL runs (not the order you write it)

You write SELECT first, but the database runs the clauses in a different order. This single diagram explains most "why can't I do that here?" errors:

Logical execution order

1FROM
Pick the table(s).
2WHERE
Filter individual rows.
3GROUP BY
Collapse rows into groups.
4HAVING
Filter the groups.
5SELECT
Choose columns / aggregates.
6ORDER BY
Sort the final result.

WHERE happens at step 2, before groups exist. HAVING happens at step 4, after the aggregates are computed. That's the whole reason they're two different keywords.

HAVING — filter the groups

HAVING runs after GROUP BY, so it filters the already-aggregated rows. Drop any group with a single member and only the two-person departments remain:

department	headcount
Engineering	2
Sales	2
Marketing	1

HAVING COUNT(*) > 1

department	headcount
Engineering	2
Sales	2

HAVING tests the aggregate — Marketing has headcount 1, so the whole group is dropped. (WHERE could not do this; the count doesn't exist yet.)

Try it now: write the query the visual showed, the departments with more than one employee (department and headcount).

Your turn · Exercise

Return the departments that have more than one employee, with columns department and headcount.

The data

employees

5 rows returned

name	department	salary
Ada	Engineering	62,000
Bo	Sales	48,000
Cy	Engineering	71,000
Di	Sales	52,000
Eli	Marketing	45,000

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING ...

Runs in your browser

Run it: departments with more than one employee.

HAVING filters on the aggregate; WHERE could not do this

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY headcount DESC

Editable · runs in your browser

Marketing (one person) is filtered out by HAVING. Try changing it to HAVING AVG(salary) > 50000 to filter on a different aggregate.

WHERE and HAVING in the same query

They're not either/or; you often want both. WHERE trims rows before grouping (cheaper, and changes what's counted); HAVING trims groups after. Run it: among non-Marketing staff, departments whose average salary tops €50k.

WHERE filters rows first, then HAVING filters the resulting groups

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE department <> 'Marketing'
GROUP BY department
HAVING AVG(salary) > 50000

Editable · runs in your browser

Watch out

Putting an aggregate in WHERE, like WHERE COUNT(*) > 1, is the classic beginner error and it errors out every time. If you're filtering on something you had to COUNT, SUM, or AVG to know, it belongs in HAVING.

From practice

Rule of thumb: filter as much as you can in WHERE (it runs first, on fewer rows, and is usually faster), and reserve HAVING strictly for conditions on the aggregated result.

Your turn

Filter on an aggregate. Return the departments whose average salary is above 50000 (department and avg_salary).

Your turn · Exercise

Return the departments whose average salary is above 50000, with columns department and avg_salary.

The data

employees

5 rows returned

name	department	salary
Ada	Engineering	62,000
Bo	Sales	48,000
Cy	Engineering	71,000
Di	Sales	52,000
Eli	Marketing	45,000

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING ...

Runs in your browser

Next: the last fundamental, combining two tables with a JOIN, and the row beginners always lose.