Oracle 26ai introduces the FILTER clause, which lets you apply a WHERE condition directly inside an aggregate function, replacing the CASE workaround.

If you want to follow along

Add a new column to the HR.EMPLOYEES table called STATUS update some of the records to ACTIVE and the rest to INACTIVE. A script to make these changes is available from GitHub

The problem

Conditional aggregation is everywhere: counting active vs inactive records, summing sales for one region while ignoring another, calculating an average only over a subset of rows. Before 26ai, there was no clean syntax for this. You had to wrap a CASE expression inside the aggregate function, which works, but it reads awkwardly and obscures what you are actually trying to compute.

-- Headcount split by employment status
-- The CASE approach
SELECT COUNT(CASE WHEN status = 'ACTIVE'   THEN 1 END) AS active_count,
       COUNT(CASE WHEN status = 'INACTIVE' THEN 1 END) AS inactive_count,
       ROUND(AVG(CASE WHEN status = 'ACTIVE' THEN salary END), 2) AS avg_active_salary
FROM   employees;

The 26AI solution

The FILTER (WHERE ...) clause moves the condition out of the aggregate and places it where it reads naturally, after the function:

-- Same result, stated plainly
SELECT COUNT(*) FILTER (WHERE status = 'ACTIVE')                  AS active_count,
       COUNT(*) FILTER (WHERE status = 'INACTIVE')                AS inactive_count,
       ROUND(AVG(salary) FILTER (WHERE status = 'ACTIVE'), 2)     AS avg_active_salary
FROM   employees;