HAVING in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this GROUP BY
What is HAVING in SQL?
HAVING filters groups after aggregation is complete. It decides which grouped results survive into the output.
You're querying a database with 62 customers and several hundred orders. The product team wants to identify repeat buyers — anyone who placed more than three orders. Getting there takes two steps: count orders per customer with GROUP BY, then keep only the groups where that count exceeds three. That second step is HAVING's job. You can't do it with WHERE, because WHERE runs before grouping starts, before any counts exist. HAVING runs after GROUP BY, when the aggregate values are ready to filter on.
The division is: WHERE filters individual rows going into the aggregation; HAVING filters groups coming out of it.
How do you filter by a COUNT with HAVING?
Here's what the repeat-buyer query looks like:
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3SQL groups orders by customer, counts the rows in each group, and HAVING removes any group where the count is 3 or under. Only repeat buyers reach the output.
HAVING works with any aggregate function. Products that generated more than $2,000 in total revenue:
SELECT product_id, SUM(quantity * unit_price) AS total_revenue
FROM order_items
GROUP BY product_id
HAVING SUM(quantity * unit_price) > 2000Why can you not use a SELECT alias in HAVING?
Notice that the aggregate expression appears twice — once in SELECT and again in HAVING. That's required. SQL resolves aliases after HAVING has already run, which means you can't write HAVING total_revenue > 2000 even though you defined that alias in SELECT. Writing the full expression in both places is standard practice, and once you know why, it stops feeling strange.
What is the difference between WHERE and HAVING?
WHERE and HAVING work together when you need to filter at both stages:
SELECT customer_id, COUNT(*) AS delivered_count FROM orders WHERE status = 'delivered' GROUP BY customer_id HAVING COUNT(*) > 2
WHERE removes non-delivered orders before any grouping happens. GROUP BY forms the groups. HAVING keeps only customers with more than two delivered orders. That sequence is also the order SQL evaluates the query.
Why can you not put COUNT(*) in a WHERE clause?
The one thing that trips people up: putting aggregate conditions in WHERE instead of HAVING.
A filter like COUNT(*) > 3 or SUM(amount) > 1000 belongs in HAVING, not WHERE. When WHERE runs, there are no groups yet and no aggregate values. SQL raises an error.
The inverse mistake is less common but worth knowing: putting non-aggregate conditions in HAVING when they belong in WHERE. Something like HAVING status = 'delivered' is technically valid, but it forces SQL to group every row first and then discard the ones that don't match. Running that filter through WHERE before grouping is more efficient, and on large tables the difference shows.
You want to keep only groups where SUM(total_amount) > 500. Where does that condition go?
Practice HAVING in SQL
Brightlane's growth team is analysing repeat-purchase behaviour to build a loyalty programme.
Write a query to return the customer ID and order count for every customer with more than three orders on record.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The threshold is on the per-customer count of orders — three orders does not qualify; four or more does.
- The condition applies to the per-customer count, not to individual orders.
Output:
- One row per qualifying customer, with columns
customer_idandorder_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
filter groups in Station Zero10 HAVING practice problems
Write a query to return the customer ID and order count for every customer with more than three orders on record.
Write a query to return the product ID and total revenue for every product whose combined order-line revenue exceeds $2,000.
Write a query to return the department ID and employee count for every department with more than five employees.
Write a query to return the customer ID and count of delivered orders for every customer with more than two fulfilled orders.
Write a query to return the product ID and average unit price for every product whose mean line price exceeds $300.
Write a query to return each status and its unique-customer count for statuses that have been placed by more than ten different customers.
Write a query to return the user ID and session count for every user who has recorded more than five sessions.
Write a query to return the total order amount if and only if this threshold is met.
Write a query to return the category ID and average list price for every category whose mean price exceeds $100.
Write a query to return each such status and its order count.
Start learning to practice all 10 HAVING problems, with instant grading and mastery tracking.
Common questions about HAVING
Can you use HAVING without GROUP BY?
Yes. With no GROUP BY the whole table counts as one group, so HAVING tests the aggregate over everything and returns either that single row or no rows at all. It is an unusual shape, but it is legal and sometimes the clearest way to ask whether a total clears a threshold.
Can HAVING filter on a column that is not in the SELECT list?
Yes. HAVING can test any aggregate over the grouped rows whether or not it appears in the output, so you can keep the customers whose total spend clears a threshold while returning only their id.
Should a filter on a plain column go in WHERE or HAVING?
WHERE. Both can be made to work, but WHERE removes rows before any grouping happens, so the database forms fewer groups and does less work. Save HAVING for conditions that need an aggregate, because those do not exist until the grouping is finished.
