SQL HAVING vs WHERE: The Difference, With Examples
· 6 min read
SQL HAVING vs WHERE comes down to timing. WHERE filters individual rows before they are grouped. HAVING filters the groups after GROUP BY has built them, so it is the only one of the two that can test an aggregate like SUM(amount) or COUNT(*).
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
HAVING SUM(amount) > 600
ORDER BY customer_name;Read it as: keep completed orders (WHERE), add them up per customer (GROUP BY), then keep customers whose total is over 600 (HAVING). Below is the same query on real rows, the HAVING COUNT pattern, and the mistake that makes "big customers" disappear.
A worked example
| order_id | customer_name | status | amount |
|---|---|---|---|
| 1 | Acme Dental | completed | 400 |
| 2 | Acme Dental | completed | 350 |
| 3 | Acme Dental | cancelled | 900 |
| 4 | Birch Cafe | completed | 700 |
| 5 | Cobalt Labs | completed | 100 |
| 6 | Cobalt Labs | completed | 150 |
| 7 | Cobalt Labs | completed | 200 |
| 8 | Dune Studio | cancelled | 1200 |
| 9 | Birch Cafe | completed | 100 |
Step 1: WHERE removes rows
Without the HAVING line, you see what WHERE and GROUP BY produce on their own:
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
ORDER BY customer_name;| customer_name | total |
|---|---|
| Acme Dental | 750 |
| Birch Cafe | 800 |
| Cobalt Labs | 450 |
The two cancelled rows (orders 3 and 8) never reached the grouping step. That is why Dune Studio is missing completely: all of its rows were filtered out.
Step 2: HAVING removes groups
Add HAVING SUM(amount) > 600 back and the full query returns:
| customer_name | total |
|---|---|
| Acme Dental | 750 |
| Birch Cafe | 800 |
Cobalt Labs is dropped because its group total, 450, fails the test. No single row was removed in this step: whole groups were.
The SQL HAVING clause in one picture
SQL runs the clauses of a query in this order, regardless of the order you write them in:
FROMandJOIN: get the rowsWHERE: filter rowsGROUP BY: build groupsHAVING: filter groupsSELECT: calculate the output columnsORDER BY, thenLIMIT
That order explains every rule about the two clauses:
| WHERE | HAVING | |
|---|---|---|
| Filters | rows | groups |
| Runs | before GROUP BY | after GROUP BY |
| Can use SUM, COUNT, AVG | no | yes |
| Works without GROUP BY | yes | yes, the whole table is one group |
If you put an aggregate in WHERE, the database refuses:
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 600
GROUP BY customer_name;DuckDB answers WHERE clause cannot contain aggregates!. Postgres says aggregate functions are not allowed in WHERE. The error is a good thing: it tells you to move the condition to HAVING.
GROUP BY HAVING with COUNT
HAVING COUNT(*) is the most common use of the clause: "customers with at least 3 orders", "products sold more than 10 times".
SELECT customer_name, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
HAVING COUNT(*) >= 3;| customer_name | completed_orders |
|---|---|
| Cobalt Labs | 3 |
Acme Dental also has three orders in the table, but one is cancelled, so after WHERE it only has two. Again, WHERE first, HAVING second.
Finding duplicates with HAVING COUNT
The same pattern finds values that appear more than once, which is a standard data quality check:
SELECT customer_name, COUNT(*) AS rows_found
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1
ORDER BY customer_name;| customer_name | rows_found |
|---|---|
| Acme Dental | 3 |
| Birch Cafe | 2 |
| Cobalt Labs | 3 |
Here it just shows repeat customers, but run it on a column that should be unique, like an order_id, and any row it returns is a real problem. More on that in COUNT DISTINCT and finding duplicates.
Can HAVING use the column alias?
DuckDB and MySQL accept HAVING total > 600, using the alias from SELECT. Postgres and SQL Server do not, because the alias does not exist yet when HAVING runs. Repeating the expression, HAVING SUM(amount) > 600, works everywhere.
Common mistakes
Filtering each row when you meant the total (the AI mistake to watch for)
Ask an AI assistant for "customers who spent over 600" and a frequent answer puts the threshold on the row amount:
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
AND amount > 600
GROUP BY customer_name;| customer_name | total |
|---|---|
| Birch Cafe | 700 |
It runs, it looks tidy, and it is wrong twice. Acme Dental spent 750 across two orders but no single order was over 600, so it vanished. Birch Cafe's total shows 700, not 800, because its 100 order was filtered out before the sum. On larger data the assistant may even tell you that "no customer passes the threshold".
How to spot it: if the question is about a total, an average or a count per group, the threshold must be in HAVING. When you see amount > X in a WHERE clause, ask whether X was meant for one row or for the group.
Moving everything into HAVING
The opposite mistake is putting row filters in HAVING. Drop the WHERE and you are adding up cancelled orders too:
SELECT customer_name, SUM(amount) AS total
FROM orders
GROUP BY customer_name
HAVING SUM(amount) > 600
ORDER BY customer_name;| customer_name | total |
|---|---|
| Acme Dental | 1650 |
| Birch Cafe | 800 |
| Dune Studio | 1200 |
Dune Studio has never completed an order, yet it tops the list. Row conditions like status, dates and regions belong in WHERE. It is also faster, because fewer rows get grouped.
Using HAVING on a column that is not grouped
HAVING status = 'completed' only works if status is in GROUP BY, and then you get one group per customer and status. It can give the right answer, but it is a roundabout way to write a WHERE.
FAQ
Can I use WHERE and HAVING in the same query?
Yes, and you often should. The main example uses both: WHERE for the status, HAVING for the total. WHERE always comes before GROUP BY in the text, and HAVING after it.
Can you use HAVING without GROUP BY?
Yes. The whole result is treated as one group. SELECT SUM(amount) FROM orders HAVING SUM(amount) > 1000 returns the total only if it is over 1,000, and no rows otherwise. It is rare in practice.
Is HAVING slower than WHERE?
For row conditions, usually yes, because the database has to group rows it could have thrown away earlier. Filter rows in WHERE and keep HAVING for aggregate conditions.
What does HAVING COUNT(*) > 1 mean?
Keep only the groups with more than one row. With GROUP BY on a column that should be unique, it lists the duplicated values.
For the basics of grouping itself, see GROUP BY multiple columns. Tricky filters like this also come up a lot in SQL interview questions for data analysts.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Your most loyal customers” →