How to Use Filter Clause to Have Multiple Counts

The filter clause extends aggregate functions (like sum, avg or count) by an additional where clause. The result of the aggregate is built from only the rows that satisfy the additional where clause too.

select count(*) total_emp,
       count(*) filter (where salary > 50000) as Upper_50K,
       count(*) filter (where salary > 100000) as Upper_100K
from employee;

But at this time Oracle does not support the filter clause in aggregate functions, however there is a way to obtain this result, by doing the following:

select count(*) total_emp,
       count(case when salary > 50000 then 1 end) as Upper_50K,
       count(case when salary > 100000 then 1 end) as Upper_100K
from employee;

IN THIS PAGE