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.

Skip to the 4 exercises ↓

Syntax

SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-02-01'
GROUP BY customer_id
HAVING SUM(amount) > 250
WHERE 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 BY and 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.

orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14

The order things happen in

  1. FROM: the rows of the table.
  2. WHERE: each row is tested, and the rows that fail are removed.
  3. GROUP BY: the rows that are left are put into groups, and COUNT, SUM and the other aggregates are computed for each group.
  4. HAVING: each group is tested, and the groups that fail are removed.
  5. SELECT and ORDER 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.

Live queryWHERE: rows are removed, then counted
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 4 rows
customer_idorderstotal
1135
21240
32300
4135
Live queryHAVING: groups are computed, then removed
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 3 rows
customer_idorderstotal
13395
22275
32300
Live queryBoth in one query: only customer 3 is left
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 1 row
customer_idorderstotal
32300

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.

Live queryAn aggregate in WHERE
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run

SQLite says: misuse of aggregate: SUM()

Which one do I use?

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.

Exercise 1Customers with more than one order

Return the customer_id and the number of orders of each customer who has placed more than one order.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
Result · 3 rows
customer_idCOUNT(*)
13
22
32

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.

Exercise 2Count only the big orders

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer_id, COUNT(*)
FROM orders
WHERE amount >= 100
GROUP BY customer_id
Result · 3 rows
customer_idCOUNT(*)
12
21
32

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.

Exercise 3WHERE and HAVING together

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
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-02-01'
GROUP BY customer_id
HAVING SUM(amount) > 100
Result · 2 rows
customer_idSUM(amount)
2240
3300

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.

Exercise 4Products that brought in more than 200

Return each product and its total amount, only for the products whose total is more than 200.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT product, SUM(amount)
FROM orders
GROUP BY product
HAVING SUM(amount) > 200
Result · 2 rows
productSUM(amount)
Chair240
Desk480

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.

Keep going

Lesson 2 is seven drills on an orders table: COUNT, SUM and AVG, GROUP BY, then HAVING to filter the groups, and COUNT(DISTINCT). It's free, like lessons 1, 3 and 4 and the level test.

Related

SQL GROUP BY, with live queriesSQL COUNT(DISTINCT), with live queriesAggregate functions are not allowed in WHEREColumn must appear in the GROUP BY clause (not a GROUP BY expression)SQL GROUP BY practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Aggregate Functions (tutorial), PostgreSQL documentation: Table Expressions (GROUP BY and HAVING), SQLite documentation: SELECT (WHERE, GROUP BY, HAVING). Last checked November 2026.