HAVING: a condition for the groups
In short: HAVING filters groups after GROUP BY with a condition on an aggregate such as SUM(total) > 100, which WHERE cannot do because it runs before grouping.
Problem
The loyalty card
The shop launches a loyalty card: customers with more than 3 orders get one. You know how to count the orders of each customer with GROUP BY customer_id. That gives 18 rows. How do you keep only those with more than 3?
WHERE will not do: it looks at single orders, and the count belongs to the whole customer.
The idea
HAVING
HAVING is a condition for the groups. It goes after GROUP BY, and it can contain an aggregate:
Four customers. The groups are made, the count is worked out for each, and HAVING keeps only the groups for which the condition is true.
Try it
The big orders
For free delivery we need the orders over 30 €. The field shows the value of each order. Keep only the ones over 30 €.
Solution
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 30;HAVING SUM(quantity * unit_price) > 30. 25 of the 60 orders. SQLite also accepts HAVING total > 30 with the alias, but not every database does. That is the topic of the next lesson.
See it
SUM in WHERE
Almost everyone's first attempt uses WHERE. Run it:
SQLite stops with “misuse of aggregate”: an aggregate in a place where it does not belong. In WHERE the database looks at the rows one by one. There are no groups yet, so there is nothing to add up.
The idea
WHERE and HAVING together
The two do not get in each other's way. They work in different places:
WHEREfilters the rows before the groups;HAVINGfilters the groups after them.
The loyalty card without the cancelled orders:
Customer 7 dropped out. They have 4 orders, but one is cancelled. WHERE removed it before the groups were made, and 3 were left in their group.
Put a condition that looks at a single row (status, a date, a price) in WHERE. It cuts down the rows the database has to group. Leave only the conditions on aggregates in HAVING.
Try it
Only once
Marketing wants to write to the customers who ordered exactly once in 2026. Count all orders, cancelled ones too. Show customer_id and the number of their orders from 2026.
Solution
SELECT customer_id, COUNT(*)
FROM orders
WHERE ordered_at >= '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) = 1;WHERE ordered_at >= '2026-01-01', then GROUP BY customer_id and HAVING COUNT(*) = 1. Six customers. Those who ordered nothing in 2026 are not there: they have no rows, so no group either. For them you will need a JOIN.
Exercise
Two reports
1. Suppliers give a discount on the products we sell a lot of. Which products have sold at least 15 items?
Show product_id and the items sold for the products with at least 15 items sold.
Solution
SELECT product_id, SUM(quantity)
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) >= 15;GROUP BY product_id HAVING SUM(quantity) >= 15. Ten products. “At least 15” includes 15, so it is >=.
2. In which months were there at least 6 orders? Those months need more people in the warehouse.
Show the month (year-month) and the number of orders for the months with at least 6 orders.
Solution
SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*)
FROM orders
GROUP BY strftime('%Y-%m', ordered_at)
HAVING COUNT(*) >= 6;GROUP BY strftime('%Y-%m', ordered_at) HAVING COUNT(*) >= 6. Four months: September and October 2025, May and September 2026. The busiest is October 2025 with 9 orders.
Test