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;