Oracle AI Database 26ai introduces aggregation filters using the SQL-standard FILTER (WHERE ...) clause. It lets you apply a condition to one aggregate function without filtering rows for the whole query.
This is especially useful when a report needs several conditional totals or counts. Before this enhancement, the usual solution was to put a CASE expression inside every aggregate function. The new syntax is shorter and makes the intention of the query much easier to read.
Syntax
aggregate_function ( aggregate_function_arguments )
FILTER (WHERE condition)The filter belongs to the aggregate function immediately before it. Only rows for which the condition is true are passed to that aggregate.
Test data
DROP TABLE IF EXISTS sales_test PURGE;
CREATE TABLE sales_test
(
id NUMBER,
region VARCHAR2(10),
status VARCHAR2(10),
amount NUMBER,
sale_date DATE
);
INSERT INTO sales_test VALUES (1, 'EAST', 'PAID', 100, DATE '2026-01-10');
INSERT INTO sales_test VALUES (2, 'EAST', 'PENDING', 50, DATE '2026-01-15');
INSERT INTO sales_test VALUES (3, 'WEST', 'PAID', 200, DATE '2026-02-05');
INSERT INTO sales_test VALUES (4, 'WEST', 'CANCELLED', 80, DATE '2026-02-07');
INSERT INTO sales_test VALUES (5, 'EAST', 'PAID', 150, DATE '2026-02-20');
INSERT INTO sales_test VALUES (6, 'WEST', 'PENDING', 70, DATE '2026-03-01');
COMMIT;Conditional aggregation before 26ai
To calculate a separate value for each status, older releases commonly use conditional CASE expressions.
SELECT
SUM(CASE WHEN status = 'PAID' THEN amount END) AS paid_amount,
SUM(CASE WHEN status = 'PENDING' THEN amount END) AS pending_amount,
COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) AS cancelled_count
FROM sales_test;PAID_AMOUNT PENDING_AMOUNT CANCELLED_COUNT
----------- -------------- ---------------
450 120 1The query works, but the condition is hidden inside the argument of each aggregate.
New FILTER clause
In Oracle AI Database 26ai the same query can be written directly:
SELECT
SUM(amount) FILTER (WHERE status = 'PAID') AS paid_amount,
SUM(amount) FILTER (WHERE status = 'PENDING') AS pending_amount,
COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_count
FROM sales_test;PAID_AMOUNT PENDING_AMOUNT CANCELLED_COUNT
----------- -------------- ---------------
450 120 1Each aggregate has its own local filter. A source row can therefore contribute to one aggregate, several aggregates, or none of them.
Multiple filtered aggregates with GROUP BY
Aggregation filters work naturally with grouped reports.
SELECT
region,
COUNT(*) AS all_rows,
COUNT(*) FILTER (WHERE status = 'PAID') AS paid_rows,
SUM(amount) FILTER (WHERE status = 'PAID') AS paid_amount,
SUM(amount) FILTER (WHERE status = 'PENDING') AS pending_amount
FROM sales_test
GROUP BY region
ORDER BY region;REGION ALL_ROWS PAID_ROWS PAID_AMOUNT PENDING_AMOUNT
------ -------- --------- ----------- --------------
EAST 3 2 250 50
WEST 3 1 200 70This is one of the most useful cases for FILTER: a single scan can produce several measures with different conditions while the grouping logic remains obvious.
WHERE, FILTER and HAVING do different jobs
A query-level WHERE clause removes rows before grouping. The aggregation filter is evaluated on those remaining rows and affects only its own aggregate. Finally, HAVING can remove complete groups based on aggregate results.
SELECT
region,
SUM(amount) AS total_amount,
SUM(amount) FILTER (WHERE status = 'PAID') AS paid_amount
FROM sales_test
WHERE sale_date >= DATE '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 200
ORDER BY region;
REGION TOTAL_AMOUNT PAID_AMOUNT
---------- ------------ -----------
EAST 300 250
WEST 350 200Here the date condition applies to every calculation, the status condition applies only to paid_amount, and HAVING decides which regions appear in the final result.
FILTER with analytic functions
The clause can also be placed before OVER when an aggregate function is used as an analytic function.
SELECT
id,
region,
status,
amount,
SUM(amount) FILTER (WHERE status = 'PAID')
OVER (PARTITION BY region) AS region_paid_amount
FROM sales_test
ORDER BY region, id;
ID REGION STATUS AMOUNT REGION_PAID_AMOUNT
---------- ---------- ---------- ---------- ------------------
1 EAST PAID 100 250
2 EAST PENDING 50 250
5 EAST PAID 150 250
3 WEST PAID 200 200
4 WEST CANCELLED 80 200
6 WEST PENDING 70 200Every detail row is preserved, while region_paid_amount contains the total of paid rows for its region.
COUNT and NULL values
The normal aggregate rules still apply after filtering. For example, COUNT(*) counts matching rows, while COUNT(amount) counts only matching rows where amount is not NULL.
SELECT
COUNT(*) FILTER (WHERE status = 'PAID') AS paid_rows,
COUNT(amount) FILTER (WHERE status = 'PAID') AS paid_amount_values
FROM sales_test;
PAID_ROWS PAID_AMOUNT_VALUES
---------- ------------------
3 3Restrictions
The filter condition can use conditions supported by a regular WHERE clause, but it cannot contain subqueries, window functions, or outer references. FILTER must follow an aggregate function; it cannot be attached to a non-aggregate function.
Execution order
A useful mental model is:
FROM / JOIN -- build the source row set
WHERE -- filter rows for the complete query
GROUP BY -- create groups
FILTER -- choose rows for each individual aggregate
aggregate function -- calculate the value
HAVING -- filter complete groups
ORDER BY -- sort the final resultSummary
The new FILTER (WHERE ...) clause removes much of the noise from conditional aggregation. It is SQL-standard syntax, keeps each condition next to the aggregate it controls, and makes reports containing many conditional measures easier to understand and maintain.
For more details, see the Oracle documentation: Advanced SQL Extensions: Calendar Functions and Aggregation Filters.
Have a fun 🙂
Tomasz