KeiruaProd

PostgreSQL snippet: multiple counts in one query

Occasionnaly you’ll want to have multiple counts with only one SQL query. That’s where the filter keyword comes into play:

select
    count(*) as "Total subscriptions",
    count(*) filter (where state = 'new') as "New subscriptions",
    count(*) filter (where state = 'pending') as "Pending subscriptions"
from user_subscriptions;

Here is postgres’ doc on the topic.

See a typo ? You can suggest a modification on Github.