Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

grantm (164)

grantm
  (email not shown publicly)
http://www.mclean.net.nz/

Just a simple [cpan.org] guy, hacking Perl for fun and profit since way back in the last millenium. You may find me hanging around in the monestary [perlmonks.org].

What am I working on right now? Probably the Sprog project [sourceforge.net].

GnuPG key Fingerprint:
6CA8 2022 5006 70E9 2D66
AE3F 1AF1 A20A 4CC0 0851

Journal of grantm (164)

Thursday March 30, 2006
03:44 AM

SQL Fun

[ #29159 ]

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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.