How to Filter Groups with HAVING in Oracle SQL

A grouped query returns one row per group, with totals, counts, or averages for that group. Often you only want some of those groups: the customers who spent more than a certain amount, or the airports with more than one route. The condition depends on an aggregate, so WHERE cannot express it. That is the job of the HAVING clause.

This guide explains how HAVING works, how it differs from WHERE, and how to combine the two in one query, with examples you can run in Oracle AI Database 26ai.

Code for This Guide

The main examples are in the examples/grouping folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

HAVING follows GROUP BY and takes a condition, which may use aggregate functions and the grouped expressions.

Syntax:

select group_expr, aggregate(expr)
from   table
[where  row_condition]
group  by group_expr
having group_condition
[order  by ...];

WHERE Filters Rows, HAVING Filters Groups

Oracle processes a grouped query in a fixed order, and the place of each clause in that order explains what it can see:

1. FROM and the joins produce the rows.
2. WHERE removes rows, one at a time. Aggregates do not exist yet, so WHERE cannot use them.
3. GROUP BY puts the remaining rows into groups.
4. The aggregates are computed for each group.
5. HAVING removes whole groups, using those aggregates.
6. SELECT and ORDER BY shape the final result.

Putting an aggregate in WHERE therefore fails.

Example:

select customer_id, count(*) as bookings
from   bookings
where  sum(total_amount) > 20000
group  by customer_id;

Output:

Error starting at line : 1
In command -
select customer_id, count(*) as bookings
from   bookings
where  sum(total_amount) > 20000
group  by customer_id
Error at Command Line : 3 Column : 8
Error report -
SQL Error: ORA-00934: group function is not allowed here

Putting a plain row condition in HAVING works, but it filters later than necessary, after the rows have already been grouped; keep row conditions in WHERE.

Keep Groups with More Than One Row

This query counts the routes that start at each airport and keeps only the airports with more than one route.

Example:

select r.origin, count(*) as routes, round(avg(r.distance_km)) as avg_km
from   routes r
group  by r.origin
having count(*) > 1
order  by routes desc, r.origin;

Output:

ORIGIN       ROUTES    AVG_KM
_________ _________ _________
DXB              20      7294
SIN               3      5832
SYD               3      6832
AKL               2      8180
BOM               2      1532
DEL               2      1660
JFK               2      8271
LHR               2      5519
NRT               2      6669

9 rows selected.

Airports with a single route form groups too, but HAVING count(*) > 1 discards them before the result is returned. Notice that ORDER BY can sort by the alias routes, because ORDER BY runs after SELECT.

Combine WHERE and HAVING

Here both clauses work together. WHERE drops cancelled bookings before grouping, so they count toward neither the number of bookings nor the amount spent. HAVING then keeps the customers whose remaining bookings add up to more than 20,000.

Example:

select customer_id, count(*) as bookings, sum(total_amount) as spent
from   bookings
where  status <> 'CANCELLED'
group  by customer_id
having sum(total_amount) > 20000
order  by spent desc;

Output:

   CUSTOMER_ID    BOOKINGS       SPENT
______________ ___________ ___________
            12           7    38151.68
            85          11    37212.89
             5          13    34865.31
            49          11    27586.61
            92           7    24864.98
            51           7    23611.98
            78           9    22881.85
            21          12    20963.38
            18          11    20564.72

9 rows selected.

Move the status condition into HAVING and the query fails, because status is neither grouped nor aggregated, so it has no single value for a group. Move the sum condition into WHERE and it fails with ORA-00934. Each condition belongs to exactly one clause.

Things to Know

• HAVING can use aggregates that do not appear in the select list, for example having max(total_amount) > 5000 while selecting only the count.
• HAVING cannot use a column alias from the select list; repeat the expression, as in having sum(total_amount) > 20000 rather than having spent > 20000.
• HAVING without GROUP BY treats the whole result as one group, returning one row or none.
• Conditions on NULL behave as everywhere else in SQL: a group whose aggregate is NULL fails a comparison such as sum(x) > 0.

Related Guides

• How to Use GROUP BY in Oracle Database 23ai
• How to Use WHERE Clause in Oracle Database 23ai Queries

Conclusion

Use WHERE to remove rows before they are grouped and HAVING to remove groups after their aggregates are computed. HAVING is the only place a condition on count, sum, avg, or any other aggregate can go, and combining it with WHERE lets you decide precisely which rows feed each group and which groups reach the result.