# 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;
```

count_high_paid BIGINT |
---|

11 |

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

```
SELECT count(*) FROM employees
```

count BIGINT |
---|

38 |

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;
```

percent_high_paid NUMERIC |
---|

28.9473684210526316 |

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;
```

percent_high_paid NUMERIC |
---|

28.9473684210526316 |

That's much easier to read and write.