SQL practice › SQL reference › WHERE vs HAVING
WHERE vs HAVING in SQL: rows before grouping, groups after
WHERE filters rows before they are grouped. HAVING filters groups after their totals are computed. So a condition on a plain column, such as amount >= 100, goes in WHERE, and a condition on an aggregate, such as SUM(amount) > 250, goes in HAVING: when WHERE runs, the sum doesn't exist yet.
A query can have both. Which one a condition goes in changes the numbers, not only which rows you see.
Syntax
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-02-01'
GROUP BY customer_id
HAVING SUM(amount) > 250WHERE order_date >= '2026-02-01'- Tested on each row. Rows that fail are gone before any group is made.
GROUP BY customer_id- The rows that are left are put into groups, and the aggregates are computed for each group.
HAVING SUM(amount) > 250- Tested on each group. It is written after
GROUP BYand can use aggregates.
The table
Eight orders from four customers. Customer 1 has three orders, customers 2 and 3 have two each, customer 4 has one.
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
The order things happen in
FROM: the rows of the table.WHERE: each row is tested, and the rows that fail are removed.GROUP BY: the rows that are left are put into groups, andCOUNT,SUMand the other aggregates are computed for each group.HAVING: each group is tested, and the groups that fail are removed.SELECTandORDER BY: what is left is returned and sorted.
The clauses are written in almost this order too: WHERE before GROUP BY, HAVING after it. The three queries below use the same table and show what each filter does to the numbers.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| customer_id | orders | total |
|---|---|---|
| 1 | 1 | 35 |
| 2 | 1 | 240 |
| 3 | 2 | 300 |
| 4 | 1 | 35 |
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| customer_id | orders | total |
|---|---|---|
| 1 | 3 | 395 |
| 2 | 2 | 275 |
| 3 | 2 | 300 |
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| customer_id | orders | total |
|---|---|---|
| 3 | 2 | 300 |
WHERE runs first and removes January. Then the groups are summed: 35, 240, 300 and 35, and only customer 3 is over 250. Customer 2 spent 275 in all and passed in the query above, but 35 of it was in January, so here the group holds 240. The HAVING only ever sees what the WHERE let through.Why an aggregate in WHERE fails
WHERE looks at one row at a time, before the groups exist. A single row has an amount, but it has no SUM(amount): that number belongs to a group. So the database refuses the query. This is one of the few mistakes on this page that does give an error.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
SQLite says: misuse of aggregate: SUM()
misuse of aggregate: SUM(). Move the condition below the GROUP BY and change the word WHERE to HAVING: three customers come back.Which one do I use?
- The condition is about single rows (a date, a product, an amount):
WHERE. - The condition is about a group's
COUNT,SUM,AVG,MINorMAX:HAVING. - The condition is about the column you group by (
customer_id <> 4): it returns the same rows in either place. Put it inWHERE. PostgreSQL's documentation notes that this is more efficient, because the rows that fail are never grouped.
The mistake that gives no error is a row condition that should have been a group condition, or the other way round. "Customers with at least two orders of 100 or more" needs WHERE amount >= 100 and HAVING COUNT(*) >= 2. "Customers with at least two orders, and their total" must not have that WHERE, or the totals shrink.
Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Return the customer_id and the number of orders of each customer who has placed more than one order.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1| customer_id | COUNT(*) |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
The number of orders only exists once the rows are grouped, so the test goes in HAVING. The same test in WHERE is an error.
Counting only the orders with an amount of 100 or more, return each customer_id and how many such orders they have. Customers with none don't need to appear.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT customer_id, COUNT(*)
FROM orders
WHERE amount >= 100
GROUP BY customer_id| customer_id | COUNT(*) |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
WHERE amount >= 100 removes the small orders first, so COUNT(*) counts only what is left: 2, 1 and 2. Customer 4's only order was 35, so no row of theirs reaches the grouping.
Look only at the orders placed on or after 2026-02-01. Return the customer_id and the total amount of each customer whose total in that period is over 100.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-02-01'
GROUP BY customer_id
HAVING SUM(amount) > 100| customer_id | SUM(amount) |
|---|---|
| 2 | 240 |
| 3 | 300 |
The date is a property of each order, so it goes in WHERE. The total is a property of the group, so it goes in HAVING. Customers 1 and 4 have 35 each in the period and are dropped by the HAVING.
Return each product and its total amount, only for the products whose total is more than 200.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT product, SUM(amount)
FROM orders
GROUP BY product
HAVING SUM(amount) > 200| product | SUM(amount) |
|---|---|
| Chair | 240 |
| Desk | 480 |
Desk (480) and Chair (240) pass. No single chair order is over 200, which is why the test has to be on the group's sum, in HAVING, and not on each row in WHERE.
Questions people ask
Can I use WHERE and HAVING in the same query?
Yes. WHERE is written before GROUP BY and removes rows; HAVING is written after it and removes groups. The aggregates in the HAVING are computed only over the rows the WHERE kept.
Why can't I use COUNT or SUM in WHERE?
Because WHERE decides which rows go into the groups, so it runs before any count or sum exists. SQLite answers misuse of aggregate. Put the condition in HAVING, or compare against a subquery that computes the number first. More on an aggregate in WHERE.
Can I use HAVING without GROUP BY?
Yes. PostgreSQL's documentation explains that a query with aggregates and no GROUP BY forms a single group out of all its rows, and HAVING then keeps or drops that one row. In SQLite, SELECT COUNT(*) FROM orders HAVING COUNT(*) > 5 returns one row, 8, on this table; with > 10 it returns nothing.
What is the difference between HAVING and GROUP BY?
They do different jobs in the same query. GROUP BY makes the groups: one row per customer. HAVING filters them: only the customers whose total is over 250. HAVING without anything to group is rarely what you want.
Can HAVING replace WHERE?
Only for a condition on the grouped column, where both return the same rows. For any other column it goes wrong quietly in SQLite: its documentation says a HAVING expression with no aggregate is evaluated against an arbitrarily selected row of the group. Keep row conditions in WHERE.