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.