When looking at the SQL standard itself, you will notice that the FILTER clause has already been around with SQL (2003). However, not many systems actually support this highly useful syntax element.
Here is an example:
test=# SELECT count(*), count(*) FILTER (WHERE id < 5), count(*) FILTER (WHERE id > 2) FROM generate_series(1, 10) AS id; count | count | count -------+-------+------- 10 | 4 | 8 (1 row)
The FILTER clause is useful if a condition cannot be used inside a normal WHERE clause because some other aggregate is in need of the data.
Before the introduction of the FILTER clause, the same could be achieved using a more cumbersome syntax:
SELECT sum(CASE WHEN .. THEN 1 ELSE 0 END) AS whatever FROM some_table; ...
