Reports often need several counts or totals over different subsets of the same rows: all flights, cancelled flights, and arrived flights, per month. The traditional way puts a CASE expression inside each aggregate. Oracle AI Database 26ai adds the SQL-standard FILTER clause, which says the same thing directly: aggregate only the rows that meet a condition.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use 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
Syntax:
aggregate_function(...) filter (where condition)
The FILTER clause follows any aggregate function, and the aggregate then sees only the rows for which the condition is true. Each aggregate in the select list can have its own filter.
Several Counts in One Pass
This query counts each month's flights, cancelled flights, and arrived flights, and adds up the kilometres flown by the arrived ones.
Example:
select to_char(sys_extract_utc(scheduled_departure), 'YYYY-MM') as month,
count(*) as flights,
count(*) filter (where status = 'CANCELLED') as cancelled,
count(*) filter (where status = 'ARRIVED') as arrived,
sum(distance_km) filter (where status = 'ARRIVED') as km_flown
from flights join routes using (route_id)
group by to_char(sys_extract_utc(scheduled_departure), 'YYYY-MM')
order by month;Output:
MONTH FLIGHTS CANCELLED ARRIVED KM_FLOWN __________ __________ ____________ __________ ___________ 2025-12 5 0 5 31523 2026-01 981 18 963 6165386 2026-02 888 17 871 5560186 2026-03 981 15 429 2717088 2026-04 1 0 0
One scan of the table produces all four columns. The months are computed in UTC, which is why five flights that leave in the early hours of 1 January in Asia count in December 2025. April has no arrived flights, so its filtered SUM is NULL.
The CASE Equivalent
The same counts without FILTER put a CASE expression inside each aggregate, returning NULL for the rows to skip.
Example:
-- the same counts without FILTER, with CASE inside the aggregate
select to_char(sys_extract_utc(scheduled_departure), 'YYYY-MM') as month,
count(case when status = 'CANCELLED' then 1 end) as cancelled,
count(case when status = 'ARRIVED' then 1 end) as arrived
from flights
group by to_char(sys_extract_utc(scheduled_departure), 'YYYY-MM')
order by month;Output:
MONTH CANCELLED ARRIVED __________ ____________ __________ 2025-12 0 5 2026-01 18 963 2026-02 17 871 2026-03 15 429 2026-04 0 0
The results match. FILTER reads more clearly, especially with several conditions, and it works the same way for every aggregate, including statistical ones such as CORR or PERCENTILE_CONT, where a CASE inside the arguments is harder to get right.
Things to Know
- FILTER applies before the aggregate, so COUNT(*) FILTER (WHERE ...) returns 0, and SUM returns NULL, when no row qualifies.
- WHERE filters rows for the whole query; FILTER filters them for one aggregate.
- The condition can use any column of the row, not only the one being aggregated.
Related Guides
- Oracle COUNT Function
- How to Filter Groups with HAVING in Oracle SQL
- How to Pivot Rows into Columns with PIVOT in Oracle
Conclusion
The FILTER clause makes an aggregate consider only the rows that meet a condition, so one query can compute counts and totals over several subsets in a single pass. It replaces the CASE-inside-the-aggregate idiom with a clearer, standard syntax.
