HAVING is the SQL clause that filters groups after aggregation, keeping only those groups whose aggregate values satisfy a condition, for example customers with more than five orders or products whose total revenue exceeds a threshold. It usually follows a GROUP BY clause and works with aggregate functions such as COUNT, SUM, AVG, MIN and MAX.
HAVING exists because the WHERE clause runs before rows are grouped and therefore cannot refer to aggregate results. In SQL, HAVING fills that gap: it is to groups what WHERE is to individual rows. It is defined in the ISO SQL standard and supported by every major relational database.
How HAVING fits into query processing
Although a SELECT statement is written starting with SELECT, the database evaluates its clauses in a logical processing order. Understanding this order explains exactly what HAVING can and cannot see.
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM and JOIN | Source rows are assembled |
| 2 | WHERE | Individual rows are filtered |
| 3 | GROUP BY | Remaining rows are grouped |
| 4 | HAVING | Groups are filtered by aggregate conditions |
| 5 | SELECT | Output columns and aliases are computed |
| 6 | ORDER BY | Result rows are sorted |
| 7 | LIMIT or FETCH | The number of returned rows is restricted |
Because HAVING runs after GROUP BY and before SELECT, it can reference grouping columns and aggregate expressions, but in standard SQL it cannot use column aliases defined in the SELECT list. Some systems, including MySQL and SQLite, accept aliases in HAVING as an extension, which makes such queries less portable.
A typical query looks like this: SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 5. The aggregate is computed per group, and only groups passing the condition reach the result.
HAVING compared with WHERE
The difference between the two clauses is the level at which they filter. Choosing correctly affects both correctness and performance.
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Evaluated | Before grouping | After grouping |
| Aggregate functions allowed | No | Yes |
| Can reference non-grouped columns | Yes | Only inside aggregates |
| Reduces work for aggregation | Yes | No, aggregation is already done |
| Typical condition | status = ‘paid’ | SUM(amount) > 1000 |
A frequent inefficiency is placing a row-level condition in HAVING, for example HAVING region = 'EU' on a grouped region column. The result is correct, but the database may aggregate every region first and discard most groups afterward. Moving such conditions to WHERE filters rows before the expensive grouping, although many query optimizers perform this rewrite automatically.
Patterns that use HAVING
HAVING appears in a handful of recurring analytical patterns. Recognizing them helps write correct queries quickly.
| Goal | HAVING condition | Notes |
|---|---|---|
| Find duplicates | COUNT(*) > 1 | Group by the columns that should be unique |
| Threshold on totals | SUM(amount) >= 10000 | Typical for customer or product rankings |
| Minimum sample size | COUNT(*) >= 30 | Avoids averages based on few rows |
| Distinct counts | COUNT(DISTINCT product_id) >= 3 | Customers who bought several products |
| Conditional aggregates | SUM(CASE WHEN status = ‘returned’ THEN 1 ELSE 0 END) > 2 | Counts only matching rows inside a group |
| Compare with a subquery | AVG(price) > (SELECT AVG(price) FROM products) | Groups above the overall average |
HAVING can also be used without GROUP BY. The whole result is then treated as a single group, so a query either returns one row or none, for example SELECT COUNT(*) FROM orders HAVING COUNT(*) > 0. This form is legal but rare, and a CTE or subquery often expresses the same intent more clearly.
HAVING and its alternatives
The same filter can often be written in other ways. A subquery or CTE can compute aggregates first and filter them with an outer WHERE, which some teams find easier to read in long queries. Both forms usually produce the same execution plan in modern optimizers.
Window functions solve a different problem. They compute aggregates without collapsing rows, so they suit questions such as showing every order together with its customer’s total. Because window functions are evaluated after HAVING, a filter on a window result requires an outer query or a CTE, and some systems offer a QUALIFY clause for that purpose.
Common mistakes with HAVING
- Using an aggregate in WHERE:
WHERE COUNT(*) > 5is an error because WHERE runs before grouping. - Relying on SELECT aliases: works in some databases but fails in others such as PostgreSQL and SQL Server.
- Forgetting NULL behavior:
SUMandAVGignore NULLs, so a group of only NULLs yields NULL and fails any comparison. - COUNT(column) versus COUNT(*): counting a nullable column skips NULL rows and changes the threshold result.
- Filtering joined duplicates: a join that multiplies rows inflates
SUMandCOUNTbefore HAVING sees them.
The last point causes many silently wrong reports. When totals look too high, the usual cause is a one-to-many join before aggregation, which is fixed by aggregating in a subquery first and joining the result.
The short version
HAVING is a filter that works on groups instead of single rows. First the database sorts records into piles, for example one pile of orders per customer. Then HAVING keeps only the piles that meet a rule, such as having more than five orders.
Picture sorting receipts into envelopes by month and then setting aside only the envelopes whose total is above 500. You cannot judge an envelope until it is filled. That is why HAVING comes after grouping, while WHERE throws out single receipts before they reach an envelope.
Where it makes a difference
A marketing analyst at a subscription company needs a list of customers for a loyalty offer: those who placed at least four paid orders in the last 12 months with a combined value of at least 300. The orders table holds about two million rows.
The query filters with WHERE for paid status and the date range, groups by customer, and applies HAVING COUNT(*) >= 4 AND SUM(amount) >= 300. Putting the date and status filters in WHERE means the database aggregates only the relevant rows, and the query returns in a couple of seconds.
A first draft had joined the order items table before grouping, which counted each item as an order and qualified roughly twice as many customers as expected. Aggregating the items in a CTE first produced the correct list, and the campaign reached exactly the intended group.