SQL HAVING Clause: Filter Groups After GROUP BY
HAVING filters groups after GROUP BY using aggregates such as COUNT and SUM. How it differs from WHERE, finding duplicates, and combining both in one query.
- Course: SQL study plan
- Module: Aggregation
- Kind: Lesson
- Reading time: 12 min
- Runtime: MySQL 8 syntax on SQLite 3.49
What is the HAVING clause in SQL?
HAVING filters groups after GROUP BY has formed them, so its conditions can use aggregate functions: GROUP BY customer_id HAVING COUNT(*) >= 3 keeps only customers with at least three orders. WHERE filters individual rows before grouping and cannot use aggregates. A query may use both: WHERE decides which rows are grouped, HAVING which groups are returned.
Lesson
GROUP BY produced one row per group. Very often only some groups matter: customers with at least three orders, products that sold more than 100 units, departments whose average salary is above a threshold, email addresses that appear more than once. The condition is about the group — a count or a total — not about any single row, so WHERE cannot express it. HAVING can. It is the WHERE of groups.
Filtering on an aggregate
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY spent DESC, customer_id;The rows are grouped by customer, the aggregates are computed per customer, and then HAVING throws away every group whose count is below 3. What survives is printed.
Trying the same with WHERE fails:
SELECT customer_id, COUNT(*) FROM orders
WHERE COUNT(*) >= 3 -- error: invalid use of group function
GROUP BY customer_id;At WHERE time each row is still on its own, and "the count of this row's group" does not exist yet.
Where HAVING sits in the order of execution
WHERE amount >= 20 GROUP BY category HAVING SUM(amount) > 60- FROM hands on all 5 orders. Two filters follow, and the pipeline across the top says when each one runs.
- WHERE runs before any grouping and sees one row at a time, so it can only test that row's own columns. The toys order of 15 fails and never reaches a group.
- GROUP BY piles the 4 surviving rows by category. Only now do totals exist — which is why WHERE SUM(amount) > 60 would be an error.
- HAVING runs after grouping and sees each finished group with its aggregates. books adds up to 55, not more than 60, so the whole group goes.
- SELECT prints one row per surviving group. Row conditions belong in WHERE, where they shrink the work early; HAVING is for conditions only a group can answer.
WHEREsees individual rows and decides which ones are grouped.HAVINGsees finished groups, with their aggregates, and decides which are kept.
Both can appear in one query, each doing its own job:
SELECT product_id, SUM(qty) AS units
FROM order_lines
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' -- rows: 2024 only
GROUP BY product_id
HAVING SUM(qty) > 100 -- groups: best sellers
ORDER BY units DESC, product_id;Read it as: of the 2024 order lines, group by product, keep the products that sold more than 100 units.
The classic: finding duplicates
"Find the duplicate emails" is one of the most asked SQL questions, and it is a GROUP BY with a HAVING:
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;Group by the value that should be unique; any group with more than one row is a duplicate. For duplicates across several columns — the same person entered twice with the same name and birth date — group by all of them: GROUP BY first_name, last_name, birth_date HAVING COUNT(*) > 1. When the duplicates differ in case or spacing, normalise in the grouping: GROUP BY LOWER(TRIM(email)).
The same shape answers "values that appear exactly once" (HAVING COUNT(*) = 1), "customers who ordered from at least three categories" (HAVING COUNT(DISTINCT category) >= 3) and "days with no failed jobs" (HAVING SUM(status = 'failed') = 0).
Conditions in HAVING
HAVING takes any condition that has one value per group — aggregates, grouping columns, and combinations of them with AND, OR and NOT:
HAVING COUNT(*) >= 3 AND SUM(amount) > 500
HAVING MAX(amount) > 2 * AVG(amount) -- a group with an outlier
HAVING MIN(order_date) >= '2024-01-01' -- customers whose first order is this year
HAVING COUNT(DISTINCT product_id) = 1 -- customers who only ever bought one productThe aggregate in HAVING does not have to be in the SELECT list. HAVING AVG(rating) >= 4 filters on the average even if only the names are printed.
Put row conditions in WHERE
A condition on a grouping column works in either clause:
SELECT region, SUM(amount) FROM orders WHERE region <> 'test' GROUP BY region;
SELECT region, SUM(amount) FROM orders GROUP BY region HAVING region <> 'test';The results are the same, but the first is better: it removes the rows before grouping, so less work is done, and it tells the reader the condition is about rows. The rule of thumb: if a condition does not need an aggregate, put it in WHERE. Interviewers ask "what is the difference between WHERE and HAVING?" expecting exactly this: WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after grouping and can.
Some conditions are not interchangeable at all. "Customers whose orders are all above 50" is a group property:
HAVING MIN(amount) > 50WHERE amount > 50 would instead drop the small orders and keep every customer who has any order above 50 — a different question with a different answer.
Aliases in HAVING
MySQL lets HAVING refer to a SELECT alias:
SELECT customer_id, SUM(amount) AS spent
FROM orders
GROUP BY customer_id
HAVING spent > 500; -- MySQL onlyStandard SQL, PostgreSQL and SQL Server reject that, because HAVING is evaluated before SELECT names its columns. Writing the aggregate again — HAVING SUM(amount) > 500 — works everywhere and costs nothing: the database computes it once.
HAVING without GROUP BY
With no GROUP BY, the whole table is a single group, and HAVING decides whether its one row is returned:
SELECT COUNT(*) AS late_orders
FROM orders
WHERE shipped_on > due_on
HAVING COUNT(*) > 0;This returns a row only if there are late orders. It is legal and occasionally useful in alerting queries, but rare.
Pitfalls
- An aggregate in
WHERE— it belongs inHAVING. - Row conditions in
HAVINGthat should be inWHERE. WHERE amount > 50when the question means "every order of the group is above 50" (HAVING MIN(amount) > 50).- Relying on SELECT aliases in
HAVINGin a database that is not MySQL. - Forgetting
ORDER BYafter filtering groups.
Key takeaways
HAVINGfilters groups;WHEREfilters rows.- Only
HAVINGcan test aggregates such asCOUNT(*)andSUM(x). - Duplicates are
GROUP BY key HAVING COUNT(*) > 1. - Put conditions that need no aggregate in
WHERE. - MySQL accepts aliases in
HAVING; portable SQL repeats the aggregate.
Common questions
What is the difference between WHERE and HAVING in SQL?
WHERE filters rows before grouping and cannot contain aggregate functions; HAVING filters groups after grouping and usually does. A condition on a plain column, such as status = 'paid', belongs in WHERE, where it also reduces the work. A condition on an aggregate, such as SUM(amount) > 500, can only go in HAVING.
How do I find duplicate values in a SQL table?
Group by the column that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. For duplicates across several columns, group by all of them. This is one of the most common SQL interview questions.
Can I use HAVING without GROUP BY?
Yes. Without GROUP BY the whole table is one group, so SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100 returns one row when there are more than 100 orders and no rows otherwise. It is legal but uncommon; most uses of HAVING follow a GROUP BY.
Can I use a column alias in HAVING?
In MySQL, yes: SELECT customer_id, SUM(amount) AS total … HAVING total > 500 works. Standard SQL, PostgreSQL and SQL Server do not allow it, because HAVING is evaluated before SELECT names its columns, so portable code repeats the aggregate: HAVING SUM(amount) > 500.
Exercises
Loyal customers
Find customers with at least 3 paid orders whose paid orders total more than 200. Orders with any other status do not count at all.
| id | customer_id | amount | status |
|---|---|---|---|
| 1 | 1 | 50 | paid |
| 2 | 1 | 80 | paid |
| 3 | 1 | 90 | paid |
| 4 | 2 | 300 | paid |
| 5 | 2 | 10 | refunded |
| 6 | 2 | 20 | paid |
| 7 | 3 | 100 | paid |
| 8 | 3 | 60 | paid |
| 9 | 3 | 70 | paid |
| 10 | 3 | 5 | paid |
| 11 | 4 | 10 | paid |
| 12 | 4 | 20 | paid |
| 13 | 4 | 30 | paid |
Return customer_id, orders and spent. Order by spent descending, then customer_id.
customer_id | orders | spent
3 | 4 | 235
1 | 3 | 220Duplicate emails
Find the email addresses that appear more than once in users. Addresses that differ only in case or surrounding spaces are the same address, so compare LOWER(TRIM(email)).
| id | |
|---|---|
| 1 | ada@x.com |
| 2 | bob@y.com |
| 3 | 'Ada@X.com ' |
| 4 | cy@z.com |
| 5 | bob@y.com |
| 6 | bob@y.com |
Return email (normalised), copies and first_id (the smallest id holding it). Order by email.
email | copies | first_id
ada@x.com | 2 | 1
bob@y.com | 3 | 2In this module: Aggregation
- Aggregate functions — COUNT, SUM, AVG, MIN and MAX
- GROUP BY — one summary row per group
- HAVING — filtering groups (this lesson)
- Conditional aggregation — SUM(CASE …) and pivots
- GROUP_CONCAT — a group's values as one string
- Grouping pitfalls — ONLY_FULL_GROUP_BY, COUNT and averages
- Checkpoint — Aggregation
← GROUP BY — one summary row per group · Conditional aggregation — SUM(CASE …) and pivots →