Why are aggregate functions not allowed in WHERE?

The query looks completely reasonable and fails every time:

SELECT dept, AVG(salary)
FROM employees
WHERE AVG(salary) > 60000
GROUP BY dept;
-- ERROR: aggregates not allowed in WHERE

Why WHERE cannot see AVG( )

A query does not execute top to bottom. The engine runs it in this order:

FROM employees      -- 1. raw rows
WHERE ...           -- 2. filter rows
GROUP BY dept       -- 3. build groups
HAVING ...          -- 4. filter groups
SELECT dept, AVG()  -- 5. output

AVG(salary) is a property of a group. When WHERE runs at step 2, there are only raw rows; the groups do not exist yet, so there is nothing to average. The error message is the engine telling you that you are asking a question one step too early.

The one-word fix

Same condition, one clause later:

SELECT dept, AVG(salary)
FROM employees
GROUP BY dept
HAVING AVG(salary) > 60000;

HAVING runs after GROUP BY, so aggregates are fair game there. One mental model to keep: WHERE keeps or drops individual rows; HAVING keeps or drops whole groups.

Rows vs groups

When a condition confuses you, ask what it applies to. "Salary above 60k" is about a row; "average salary above 60k" is about a group. The first goes to WHERE, the second to HAVING.

The trap: row filters in HAVING

This runs without an error, and it is quietly wasteful:

-- groups EVERY dept, then drops most
HAVING dept = 'sales'

-- filters first, groups one dept
WHERE dept = 'sales'

Both return the same result. But the HAVING version makes the engine group the entire table and then throw the groups away, where WHERE would have discarded the rows before any grouping happened. Save HAVING for conditions that genuinely need an aggregate.

Use both in one query

The normal shape of a real aggregation query:

SELECT dept, AVG(salary)
FROM employees
WHERE hired_at >= '2024-01-01'
GROUP BY dept
HAVING AVG(salary) > 60000
ORDER BY 2 DESC;

Recent hires only (a row condition, so WHERE), then well-paid departments only (a group condition, so HAVING). This all falls out of the execution order; sheet 005 covers that full pipeline if you want the complete picture.

The takeaway

Filter rows, then filter groups. WHERE runs before the groups exist and can never see an aggregate; HAVING runs after and exists exactly for them. The print-ready PDF above fits the order, the fix and the trap on one page.