Why can a HAVING clause refer to a SELECT alias when a WHERE clause cannot?
Because of the order in which the engine does the work. The WHERE clause runs before the SELECT clause, and the HAVING clause runs after it.
When the engine reaches the WHERE clause, the columns of the answer have not been named yet, so an alias that you wrote in the select list does not exist. When it reaches the HAVING clause, the aliases do exist, because the select list has already been worked out.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- This does not work. The alias does not exist yet at this point.
-- SELECT customer_id, SUM(amount) AS total
-- FROM orders
-- WHERE total > 100
-- GROUP BY customer_id;
-- This works, because the HAVING clause runs after the SELECT.
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING total > 100;
So what does that mean in practice? It means that in a HAVING clause you can write the calculation twice, once as the alias and once as the expression, and you have to be careful that the two are the same.
There is one more thing about the alias, and it is worth knowing because it catches people out. The rule only applies when the alias is in the select list of the same query. If you write a query where the select list is not there, then there is no alias to use, and you have to write the calculation out in full.
The next question in this topic asks for a real query that uses the clause.