use Perl

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 :-)