FILTER

What if we want to calculate the percentage of employees that get paid $200,000 per year or more? We already know how to count the number of employees per company in the employees table.

SELECT count(*) AS count_high_paid
FROM employees
WHERE salary >= 200000;

To convert that to a percentage, we would need the total count, divide the 2 quantities, and multiple by 100.

SELECT count(*) FROM employees

Is there a way to do it all in 1 query, though? We need a count that just counts all the rows, and a count that conditionally counts some rows (WHERE salary >= 200000), and to divide those results. But if we add WHERE salary >= 200000, we can't count the total number of rows in the table anymore. We can use a style similar to the solution of the Slugging Average challenge.

SELECT
  sum(
    CASE
      WHEN salary >= 200000 THEN 1
      ELSE 0
    END
  ) * 100.0 / count(*) AS percent_high_paid
FROM employees;

This works because sum(1) is equivalent to count(*). Summing 1 for each row is effectively a count. Here, we replace 1 with a CASE expression which can return 1 or 0 depending on a condition.

This works, but there is a better way. Aggregate functions having a FILTER clause which allows you to add a WHERE clause which only applies to that specific aggregate function.

SELECT
  count(*) FILTER (WHERE salary >= 200000) * 100.0 / count(*) AS percent_high_paid
FROM employees;

That's much easier to read and write.