skip to today's puzzle

HAVING filters the groups

Starting table: orders

Starting orders data before the example query
idcustomer_id
1011
1022
1032
1042

Say you want only the customers with more than two orders. WHERE can't help: it tests one row at a time, before any groups exist, so there is no group total for it to check yet. HAVING runs after GROUP BY has made the groups, and tests each group as a whole, so HAVING COUNT(*) > 2 keeps just the busy customers.

A tiny example

In plain English: For each customer, show their number of orders, but only show customers with more than two orders.

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

Query result

customer_idorders
23

Customer 2 has three orders, so the group passes. Customer 1 has one order, so that group is excluded by HAVING.

Common questions

Why can't I use WHERE instead of HAVING?
WHERE runs before rows are grouped, so it can't see aggregates like COUNT(*). HAVING runs after GROUP BY, which is why it can test them.
Can a query use both WHERE and HAVING?
Yes, and it often should. WHERE trims rows before grouping, then HAVING trims the groups afterwards, for example keeping only cities with more than one order.

Related SQL lessons

Practice HAVING step by step

The Foundations Track has beginner puzzles built around this idea. Tap tiles, see real results. No terminal, no setup.

practice this in the course

Ready for a break?

A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.

Try today's free SQL puzzle