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

Log In

#### 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.

#### SQL Fun3 Comments More | Login | Reply/

Full
Abbreviated
Hidden
More | Login | Reply
Loading... please wait.
• #### Very cool(Score:1)

Thanks for sharing! That is neat.

• #### Re: SQL Fun(Score:1)

That looks like the beginnings of an SQL Crosstab [perlmonks.org] ...
--

broquaint out

• #### Or use nullif, for less typing(Score:2)

Replace
sum(
CASE WHEN sex = 'M' THEN 1 ELSE 0 END
)
with
count(nullif(sex = 'M', false))
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.
--
• Randal L. Schwartz
• Stonehenge