HAVING and Evaluation Order

The clauses do not run in the order they are written. This one fact explains most confusing SQL errors.

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE runs before grouping, so it cannot see an aggregate: WHERE COUNT(*) > 2 is an error, because at that point there are no groups yet. HAVING is the clause that filters groups, after they exist.

SELECT runs near the end, which is why a column alias you define there cannot be used in WHERE, but can be used in ORDER BY. That asymmetry surprises everyone exactly once.

Try it

Move the year condition into HAVING and see it break, then work out from the evaluation order why.

loading editor…

real SQLite, in your browser

WHERE filters rows going in. HAVING filters groups coming out. They are not interchangeable and they are not competing.

◈ Graded Query

For each author who has more than one book, return their name and how many books they have, with the highest count first and names alphabetical where counts tie. Two columns: name, then the count.

Checked against the rows your query returns. 1 visible and 3 hidden checks, some against data you cannot see.

loading editor…

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back