Filtering Groups with HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregation — so it can use aggregate results.

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

+-------------+--------+
| customer_id | orders |
+-------------+--------+
|           1 |      3 |
|           3 |      2 |
+-------------+--------+

"Customers with at least two orders" — a condition on the count, which only exists after grouping.

WHERE and HAVING together

SELECT customer_id, COUNT(*) AS active_orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY active_orders DESC;

+-------------+---------------+
| customer_id | active_orders |
+-------------+---------------+
|           1 |             3 |
+-------------+---------------+

1. WHERE removes cancelled orders.
2. GROUP BY groups the remaining orders by customer.
3. HAVING keeps customers with two or more.

Customer 3 had two orders, but one was cancelled — so they're filtered out at step 1 and don't reach the threshold.

Which to use

• Condition on individual rows (a column's value) → WHERE. It's also faster, since fewer rows are grouped.
• Condition on a group's aggregate (COUNT, SUM, AVG...) → HAVING.

Common mistakes

• Using an aggregate in WHERE: WHERE COUNT(*) > 2 is an error ("misuse of aggregate"). Move it to HAVING.

Practice

Find product categories whose average price is above 100.

SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

+----------+-----------+
| category | avg_price |
+----------+-----------+
| Audio    |     199.0 |
| Displays |    249.99 |
+----------+-----------+