Filtering on Aggregate Results

There are three practical spellings, and they suit different situations. HAVING filters groups directly. A CTE computes the aggregate first and lets you filter on the named value in the outer query, which reads better and allows filters on several measures at once. A window function computes the aggregate alongside every row, and you filter on it in an outer query.

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, 610.00),
  (5, 2,  75.25),
  (6, 3,  40.00);

-- Option 1: HAVING filters on the aggregate directly.
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 400
ORDER BY total DESC;

-- Option 2: a CTE computes the aggregate, so WHERE can use the alias.
WITH totals AS (
  SELECT customer_id, SUM(amount) AS total, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total, order_count
FROM totals
WHERE total > 400 AND order_count >= 1
ORDER BY total DESC;

Both queries return the same two rows: customer 2 with 685.25 and customer 1 with 550.50. Customer 3's total of 40.00 falls below the threshold and is discarded.

Choose HAVING when there is a single condition and the query is short. Choose the CTE when you want to filter on more than one measure, when the condition references a derived expression, or when you want to reuse the aggregate for a second calculation without repeating the GROUP BY. Choose a window function when you need the aggregate value on every row — for example, showing each order next to the customer's running total — because a window function does not collapse the rows.

Two things that catch people. A window function cannot be filtered inside the same query that computes it, because window functions are evaluated after GROUP BY and after WHERE, so the filter has to go in an outer query. And filtering on an aggregate alias is not portable to every clause: it works in ORDER BY nearly everywhere and in HAVING in most engines, but in WHERE it only works if the alias comes from a derived table or CTE.

Because the aggregate has to exist before the filter can run. There is no index on "customers whose total spend exceeds 400", because that value does not exist in any row — it is a property of a group, computed at query time. So the engine has to read every matching row, group them, compute the aggregate for each group, and only then apply the filter and throw away the groups that failed.

The cost is therefore proportional to the number of rows scanned, not to the number of rows returned. A query that returns three customers but scans fifty million orders is expensive, and the LIMIT at the end does not help, because the aggregate has to be computed before the limit applies.

What actually helps, in order of impact. Narrow the input first: add a WHERE clause that eliminates rows the aggregate does not need, such as a date range or a status filter. Index the columns that filter does the work on, and make sure the predicate is sargable — a bare column comparison, not a function wrapped around it. Then index the grouping key so the engine can read the rows already in group order instead of sorting them.

If the same aggregate is needed over and over, materialise it. A summary table maintained by a scheduled job, or a view over a pre-aggregated table, turns a fifty-million-row scan into a few-thousand-row lookup. This is the standard answer when the report runs frequently and the data changes slowly enough to tolerate a delay.

There is a fourth option worth knowing for large data sets: a window function with a QUALIFY-style filter in an outer query. That lets you compute the aggregate per group while keeping every row, which is what you need when the output has to show the rows inside the qualifying groups. It does not make the underlying scan cheaper, but it removes the need to run the aggregation twice.