SQL HAVING Clause: Filter Groups After GROUP BY (Examples)

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 job, because it runs before grouping and knows nothing about totals. The rule is simple: use WHERE to choose which rows go into the groups, and HAVING to choose which groups appear in the result.

This guide is for anyone who already writes basic SELECT and GROUP BY queries and now wants answers like “customers with more than one order” or “products that earned at least 300”. You get the syntax, a small sample table you can recreate, ten worked queries with their exact output, a HAVING vs WHERE comparison, the errors people hit most often, and a short practice exercise with answers.

SQL HAVING Syntax

SELECT column1, AGGREGATE(column2)
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1;

HAVING always comes after GROUP BY and before ORDER BY. Its condition normally contains an aggregate function, although it may also reference a column that appears in GROUP BY.

Sample Table Used in Every Example

All queries below run against this small Orders table, so you can check every result by hand.

Example 1: Customers with More Than One Order

SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;

GROUP BY first builds five groups (customers 1, 2, 3, 4 and 6). HAVING then keeps only the group whose count is greater than 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;

The two laptop orders (1200 + 1150) are added together before HAVING tests the total, which is why Laptop passes with 2350.

Example 3: WHERE and HAVING Together

WHERE removes the two laptop orders before grouping; HAVING then keeps the 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;

March disappears entirely because its only order was a laptop, removed by WHERE before HAVING ever ran. (SQL Server uses LEFT(OrderDate, 7) or FORMAT() instead of SUBSTR.)

Example 4: HAVING with AVG

SELECT CustomerID, AVG(Amount) AS AvgOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(Amount) < 100;

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;

You can join conditions with AND, OR and NOT exactly as you would in WHERE. Wrap mixed AND/OR conditions in brackets so the logic is unambiguous.

More Practical HAVING Variations

Count distinct values per group

Find customers who bought at least two different products:

SELECT CustomerID, COUNT(DISTINCT Product) AS Products
FROM Orders
GROUP BY CustomerID
HAVING COUNT(DISTINCT Product) >= 2;

Result: customer 1 with 2 products (Laptop and Monitor).

Test the smallest value in a group with MIN

Customers whose smallest order is at least 100:

SELECT CustomerID, MIN(Amount) AS SmallestOrder
FROM Orders
GROUP BY CustomerID
HAVING MIN(Amount) >= 100;

Result: customer 1 (smallest order 300) and customer 4 (1150).

Compare each group with a subquery

Customers whose total spend is higher than the average single order (2800 / 6 = 466.67):

SELECT CustomerID, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > (SELECT AVG(Amount) FROM Orders);

Result: customer 1 (1500) and customer 4 (1150). The subquery runs once and gives HAVING a single number to compare against. See SQL subqueries in WHERE, FROM and SELECT for more patterns like this.

Conditional counts with CASE

Months that contain at least one order above 1000:

SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth,
       COUNT(*) AS Orders
FROM Orders
GROUP BY SUBSTR(OrderDate, 1, 7)
HAVING SUM(CASE WHEN Amount > 1000 THEN 1 ELSE 0 END) >= 1;

Result: 2026-01 (3 orders) and 2026-03 (1 order). February is dropped because neither of its orders exceeds 1000, yet every January order is still counted, which a WHERE filter could not do.

Busy months only

SELECT SUBSTR(OrderDate, 1, 7) AS OrderMonth, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM Orders
GROUP BY SUBSTR(OrderDate, 1, 7)
HAVING COUNT(*) >= 2;

Result: 2026-01 (3 orders, 1525) and 2026-02 (2 orders, 125).

HAVING vs WHERE

When a condition does not involve an aggregate, put it in WHERE: it removes rows earlier and the query has less to group. The SQL WHERE clause guide covers row filters in detail.

Order of Evaluation

A query with both clauses runs in a fixed sequence:

1. FROM finds the rows (including any joins).
2. WHERE discards rows that fail the row condition.
3. GROUP BY collapses the survivors into groups and computes the aggregates.
4. HAVING discards groups that fail the aggregate condition.
5. 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. The SQL syntax and clause order guide shows where every clause fits.

Works in MySQL, SQL Server, PostgreSQL and SQLite

• HAVING is standard SQL and behaves the same way in all four.
• MySQL and SQLite allow a SELECT alias in HAVING (HAVING Revenue >= 300). SQL Server and PostgreSQL require the full expression (HAVING SUM(Amount) >= 300), which is also the portable choice.
• Without GROUP BY, HAVING treats the whole result as one group. MySQL is the most permissive here; the others expect the SELECT list to contain only aggregates.

Common Errors and How to Fix Them

Tips for Writing Clean HAVING Queries

• Filter as early as possible: row conditions in WHERE, aggregate conditions in HAVING.
• Show the aggregate you are testing in the SELECT list while you build the query, so you can see why groups pass or fail.
• Use COUNT(DISTINCT column) when duplicates would inflate a count.
• Remember that SUM and AVG ignore NULLs; a group of all-NULL values returns NULL and fails most comparisons. SQL NULL values explains how to handle this with COALESCE.
• Add ORDER BY at the end so the filtered groups read in a sensible order.

If you work in spreadsheets as well, GROUP BY with HAVING is the same idea as a pivot table with a value filter. The Excel Pivot Tables and Dashboards course on NextGenTemplates Academy walks through that side step by step.

Practice Exercise

1. List the products that were ordered exactly once.
2. List the customers whose total spend is above 1000.

-- 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)

Want to test yourself further? Try the SQL quiz with 75 practice questions.

Related Chapters

• SQL GROUP BY clause – the clause HAVING depends on
• SQL COUNT, AVG and SUM functions – the aggregates HAVING tests
• SQL MIN and MAX functions – smallest and largest values per group
• SQL from Scratch: full course overview

Frequently Asked Questions

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 such as COUNT(*) > 1.

Can I use HAVING without GROUP BY?

Yes, but then the whole result is treated as a single group, so the query returns one row or none. It is rarely useful; in practice HAVING almost always follows GROUP BY.

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.

Can I use a column alias in HAVING?

MySQL and SQLite accept a SELECT alias in HAVING. SQL Server and PostgreSQL do not, so repeating the aggregate expression is the safest, portable choice.

Can HAVING have more than one condition?

Yes. Combine conditions with AND, OR and NOT, for example HAVING COUNT(*) >= 2 AND SUM(Amount) > 500. Use brackets when you mix AND with OR.

Is HAVING slower than WHERE?

For the same logical condition, HAVING usually does more work because it runs after every row has been grouped. Keep non-aggregate conditions in WHERE so fewer rows reach the grouping step.

Wrapping Up

HAVING answers questions about groups: which customers ordered more than once, which products passed a revenue target, which months were busy. Write the GROUP BY first, check the aggregate in your SELECT list, then add HAVING to keep only the groups you need. Next, continue the course with the SQL EXISTS operator.

📅 Last updated: October 2026

Chapter 18 of 32 · SQL from Scratch: all 32 chapters