Interesting things I've learned: The FILTER clause in SQL

( H/T @winand.at )

In SQL, if you wanted to perform two or more aggregations with different filters, you might be able to use the FILTER clause.

SELECT
    count(*) as user_count,
    count(*) filter (where verified = true) as verified_user_count
FROM users;

is the equivalent of:

SELECT
    count(*) as user_count,
    sum(case when verified = true then 1 else 0 end) as verified_user_count
FROM users;

except that a) it reads more naturally, and b) you don't need to write a complicated case expression.

FILTER is supported in Postgres 9.4 and above, and in sqlite. It is unfortunately not widely supported in other databases.