HAVING vs WHERE — filtering groups after aggregation

WHERE Filters Rows. HAVING Filters Groups.

This is one of the most confused distinctions in SQL. WHERE runs before GROUP BY — it filters individual rows. HAVING runs after GROUP BY — it filters the groups produced by aggregation.

-- Wrong: WHERE can't reference COUNT(*) because it runs before grouping
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 5  -- syntax error
GROUP BY customer_id;

-- Correct: use HAVING to filter on the aggregate
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Using Both WHERE and HAVING Together

You can combine both in the same query. WHERE cuts individual rows first (reducing work), then GROUP BY groups what remains, then HAVING filters those groups:

-- High-value customers who ordered in 2024
SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
WHERE created_at >= '2024-01-01'     -- filter rows first
GROUP BY customer_id
HAVING SUM(amount) > 1000            -- then filter groups
ORDER BY total_spent DESC;

Pushing conditions into WHERE rather than HAVING is a performance habit — WHERE reduces the rows that need to be grouped.