HAVING

WHERE filters rows before grouping, so it can't see aggregate results. HAVING filters groups after they've been formed: categories with more than three products, customers who've ordered at least twice, months with revenue over a target.

Filtering groups

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Customers with at least two orders. Trying this with WHERE COUNT(*) >= 2 is an error, because WHERE runs before the counting happens.

WHERE and HAVING together

SELECT category, COUNT(*) AS products, round(AVG(price), 2) AS avg_price
FROM products
WHERE stock > 0
GROUP BY category
HAVING AVG(price) > 30
ORDER BY avg_price DESC;

The order of operations is: WHERE removes out-of-stock products, GROUP BY forms categories, HAVING keeps categories averaging over 30, and ORDER BY sorts what's left.

The logical order of a query

Databases may execute queries differently internally, but results always behave as if the clauses ran in this order. It explains most "why can't I use that here?" errors.

Which filter belongs where?

SELECT department_id, COUNT(*) AS staff
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT(*) >= 2;

A condition on individual rows (salary) goes in WHERE; a condition on the group (headcount) goes in HAVING. Putting a row condition in HAVING works in some databases but is slower and confusing — filter rows as early as possible.

Big spenders

SELECT o.customer_id,
       round(SUM(oi.quantity * oi.unit_price), 2) AS total_spent
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 100
ORDER BY total_spent DESC;

Finding duplicates

SELECT salary, COUNT(*) AS people
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

GROUP BY … HAVING COUNT(*) > 1 is the standard way to find duplicate values — invaluable for checking data before adding a UNIQUE constraint.

Try it yourself

Which products have sold more than two units in total across all orders?