How Does HAVING Filter Groups?

Because of when each clause runs. The logical order of evaluation is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. The alias you define in the SELECT list does not exist until the SELECT stage, so WHERE — which runs before it — has nothing to refer to. HAVING runs after GROUP BY but before SELECT, yet it can still reference the alias, because most engines resolve aliases in HAVING and ORDER BY by looking ahead to the projection list rather than requiring the value to be physically materialised at that point.

The exception that proves the rule is ORDER BY, which also runs after the projection and can use the alias directly. WHERE and JOIN ON are the two clauses that genuinely cannot, because they run before the projection exists. GROUP BY is an interesting middle case: most engines allow an alias there too, but it is safer to repeat the expression or use an ordinal position, because the standard does not require alias support in GROUP BY and some engines reject it.

This is why the classic workaround is to wrap the query. If you want to filter on a computed column, compute it in a CTE or a derived table, then filter on the alias in the outer query — that works in WHERE because by then the alias is a real column of the derived relation.

One more trap: if the alias name shadows a real column name, the engine usually prefers the column, and the reference silently means something other than what you expected. That is another good reason to name derived columns distinctly from your base columns.

The threshold applies to a SUM, so it belongs in HAVING. The alias makes the intent readable and lets the same expression appear in the SELECT list.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 1,  90.00),
  (4, 2,  75.25),
  (5, 2, 610.00),
  (6, 3,  40.00);

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 400
ORDER BY total_spent DESC;

Two rows come back: customer 2 with a single order of 610.00, and customer 1 with three orders totalling 550.50. Customer 3's 40.00 is below the threshold. Note that the HAVING clause filters on the aggregate rather than on the alias, even though the alias would work too — repeating the SUM makes it unambiguous and portable to engines with stricter alias resolution.

Two things to watch. First, the same query written with a WHERE clause would be wrong in a way that is easy to miss: WHERE SUM(amount) > 400 is a syntax error in most engines, because the aggregate does not exist yet at the WHERE stage. Second, and more subtly, you cannot write WHERE total_spent > 400 in the same query either — the alias is not available there, which is exactly the rule the previous answer is about.

If the filter does not involve an aggregate at all, use WHERE instead. WHERE amount > 400 can use an index on amount and prunes rows before any grouping happens; a HAVING clause on the same data has to aggregate every row first and then discard groups. On a large table that is the difference between a seek and a full scan.

Use HAVING when the condition can only be evaluated after the rows have been grouped — that is, when it involves an aggregate function or a value that only exists in the result of the grouping. Use WHERE whenever the condition can be evaluated on individual rows, because WHERE runs first and prunes rows before any grouping happens.

The practical difference is performance and correctness, in that order. WHERE reduces the number of rows the engine has to aggregate, so WHERE amount > 100 is usually dramatically cheaper than HAVING SUM(amount) > 100 on the same data. And the two are not interchangeable: WHERE changes what goes into the aggregates, while HAVING only discards finished groups. Moving a predicate from one to the other changes the numbers in the report, not just which rows survive.

There is a third place to put the filter, and it is often the best answer: push it into a subquery. SELECT customer_id, SUM(amount) FROM (SELECT * FROM orders WHERE amount > 100) GROUP BY customer_id filters rows early and keeps the group-level filtering available, giving you both benefits. CTEs make the same thing readable.

A specific case worth knowing: filtering on a window function result. Window functions are evaluated after GROUP BY, so they cannot appear in WHERE or HAVING at all. The top-row-per-group pattern has to be written as a CTE with the window function inside it, then filtered outside — or, better, with ROW_NUMBER() and a WHERE on the rank in the outer query.

One practical warning. Some people put everything in HAVING because it feels more expressive. That habit produces slow queries and occasionally wrong ones. The decision rule is simple: can the condition be evaluated per row? If yes, it belongs in WHERE.