SQL Tutorial · Chapter 18 of 32
The SQL HAVING clause filters the groups produced by GROUP BY, using conditions on aggregate values such as COUNT(*) or SUM(Amount). WHERE cannot do this because it runs before grouping and knows nothing about totals. Use WHERE to choose which rows go into the groups and HAVING to choose which groups appear in the result.
SQL HAVING Syntax
SELECT column1, AGGREGATE(column2)
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1;HAVING always follows GROUP BY. Its condition normally contains an aggregate function, although it may also reference a grouped column.
Sample Table
| OrderID | CustomerID | Product | Amount | OrderDate |
|---|---|---|---|---|
| 101 | 1 | Laptop | 1200 | 2026-01-05 |
| 102 | 2 | Mouse | 25 | 2026-01-07 |
| 103 | 1 | Monitor | 300 | 2026-01-12 |
| 104 | 3 | Keyboard | 45 | 2026-02-02 |
| 105 | 6 | Headset | 80 | 2026-02-15 |
| 106 | 4 | Laptop | 1150 | 2026-03-01 |
Example 1: Customers with More Than One Order
SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;| CustomerID | Orders |
|---|---|
| 1 | 2 |
GROUP BY first builds five groups; HAVING then keeps only the group whose count exceeds 1.
Example 2: Products with Revenue of at Least 300
SELECT Product, SUM(Amount) AS Revenue
FROM Orders
GROUP BY Product
HAVING SUM(Amount) >= 300
ORDER BY Revenue DESC;| Product | Revenue |
|---|---|
| Laptop | 2350 |
| Monitor | 300 |
Example 3: WHERE and HAVING Together
WHERE removes the two laptop orders before grouping; HAVING then keeps months whose remaining revenue is above 50.
SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth, SUM(Amount) AS Revenue
FROM Orders
WHERE Product <> 'Laptop'
GROUP BY SUBSTR(OrderDate, 1, 7)
HAVING SUM(Amount) > 50
ORDER BY OrderMonth;| OrderMonth | Revenue |
|---|---|
| 2026-01 | 325 |
| 2026-02 | 125 |
March disappears entirely because its only order was a laptop, removed by WHERE before HAVING ever ran.
Example 4: HAVING with AVG
SELECT CustomerID, AVG(Amount) AS AvgOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(Amount) < 100;| CustomerID | AvgOrder |
|---|---|
| 2 | 25 |
| 3 | 45 |
| 6 | 80 |
Example 5: Several HAVING Conditions
SELECT Product, COUNT(*) AS Orders, MAX(Amount) AS Largest
FROM Orders
GROUP BY Product
HAVING COUNT(*) = 1 AND MAX(Amount) < 100;| Product | Orders | Largest |
|---|---|---|
| Headset | 1 | 80 |
| Keyboard | 1 | 45 |
| Mouse | 1 | 25 |
HAVING vs WHERE
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Can use aggregates? | No | Yes |
| Typical condition | Amount > 100 | SUM(Amount) > 100 |
When a condition does not involve an aggregate, put it in WHERE: it removes rows earlier and the query runs faster.
Works in MySQL, SQL Server, PostgreSQL, SQLite
- HAVING is standard and behaves the same in all four.
- MySQL, PostgreSQL and SQLite allow a column alias in HAVING (
HAVING Revenue >= 300); SQL Server requires the full expression. - MySQL permits HAVING without GROUP BY, treating the whole table as one group; the others expect a GROUP BY or an aggregate-only SELECT.
Order of Evaluation
A query with both clauses runs in a fixed sequence: FROM finds the rows, WHERE discards the ones that fail the row condition, GROUP BY collapses the survivors into groups and computes the aggregates, HAVING discards groups that fail the aggregate condition, then SELECT, ORDER BY and LIMIT shape the output. Because HAVING runs after aggregation, it can only see grouped columns and aggregate values; because WHERE runs before, it cannot see aggregates at all. Filtering as early as possible is cheaper, so any condition that does not need an aggregate belongs in WHERE even though HAVING would accept it.
Common Mistakes
- Writing
WHERE COUNT(*) > 1, which raises “aggregate functions are not allowed in WHERE”. - Using HAVING for a plain row condition such as
HAVING Product = 'Laptop'; it works but WHERE is clearer and faster. - Referencing a column in HAVING that is neither grouped nor aggregated.
- Placing HAVING before GROUP BY; the order is fixed.
Practice Exercise
- List products ordered exactly once.
- List customers whose total spend is above 1000.
Show answers
-- 1
SELECT Product FROM Orders GROUP BY Product HAVING COUNT(*) = 1;
-- Mouse, Monitor, Keyboard, Headset
-- 2
SELECT CustomerID, SUM(Amount) AS Total FROM Orders
GROUP BY CustomerID HAVING SUM(Amount) > 1000;
-- 1 (1500), 4 (1150)Related Chapters
- SQL GROUP BY – the clause HAVING depends on
- SQL WHERE – row-level filtering
- SQL COUNT, AVG, SUM – the aggregates HAVING tests
- SQL from Scratch: full course overview
FAQ
What is the difference between HAVING and WHERE?
WHERE filters rows before they are grouped and cannot use aggregate functions. HAVING filters the groups after GROUP BY and is designed for aggregate conditions.
Can I use HAVING without GROUP BY?
In most databases HAVING requires GROUP BY or an aggregate-only query, where the whole table counts as one group. MySQL is more permissive, but the pattern is rarely useful.
Can WHERE and HAVING be used in the same query?
Yes, and it is common: WHERE narrows the rows, GROUP BY groups them, then HAVING keeps only the groups that meet an aggregate condition.
Chapter 18 of 32 · SQL from Scratch: all 32 chapters
