I recently needed to query a database for a table of statistics like this:
city | subscribers | males | females | full | trial ----------+-------------+-------+---------+------+------- New York | 7 | 4 | 3 | 3 | 4 Eketahuna | 1 | 1 | 0 | 1 | 0 Paris | 4 | 3 | 1 | 1 | 3 London | 4 | 3 | 1 | 2 | 2
It's obviously pretty easy to count one thing. For example, total number of subscribers per city:
SELECT city, count(*) FROM subscriber GROUP BY city
But how do you count multiple different things in a single query? I discovered today that some of my colleagues weren't familiar with this trick, so I thought I'd share it here.
The trick is to use sum() instead of count() and then to craft an expression to be sum'd which evaluates to 1 for the rows you want to count and zero for the ones you don't:
SELECT
city,
count(*) AS subscribers,
sum(
CASE WHEN sex = 'M' THEN 1 ELSE 0 END
) as males,
sum(
CASE WHEN sex = 'F' THEN 1 ELSE 0 END
) as females,
sum(
CASE WHEN subscription_type = 'Full' THEN 1 ELSE 0 END
) as full,
sum(
CASE WHEN subscription_type = 'Trial' THEN 1 ELSE 0 END
) as trial
FROM subscriber
GROUP BY city;
I believe the CASE is ANSI standard SQL, it works for me in PostgreSQL anyway :-)
Thanks for sharing! That is neat.
withsum(
CASE WHEN sex = 'M' THEN 1 ELSE 0 END
)
This says, "return null if sex = m is false", and counts those. It's a bit inverted on the logic, but far less typing, and you're saying "count" again, which is really what you're doing.count(nullif(sex = 'M', false))