Re: Cross tabulations in SQL?

From: D Guntermann <guntermann_at_hotmail.com>
Date: Tue, 12 Aug 2003 02:23:51 GMT
Message-ID: <HJHInq.FMB_at_news.boeing.com>

I forgot the sub-totals and totals in the last positing. Here is a revised query.

 SELECT county, COUNT(DECODE(gender, 'M', name)) Males,

        COUNT(DECODE(gender, 'F', name)) Females,
        COUNT(DECODE(gender, 'U', name)) Unknown,
        COUNT(name) total_by_county

 FROM membership
 GROUP BY ROLLUP(county);

COUNTY MALES FEMALES UNKNOWN TOTAL_BY_COUNTY --------------- --------- --------- --------- ---------------

CORK                    0         1         0               1
DUBLIN                 1         0         0               1
MAYO                   0         1         1               2
                               1         2         1               4

Regards,

Dan Guntermann

"Paul" <paul_at_not.a.chance.ie> wrote in message news:MPG.19a1e26c3f31208098970a_at_news1.eircom.net...
>
>
> Hi all,
>
>
> I have a table for a membership system with many fields, but the
> relevant ones here are Gender (M, F, U (U is from a legacy system)),
> Category (Life, Senior, Junior & Leader) and County (26 values here,
> I'll use 3 as an example - Dublin, Cork and Mayo).
>
> What I want is something like
>
> Gender by County
>
>
> | M | F | U |
> -------------------------------
> Dublin | 12 | 14 | 5 | 31
> Cork | 5 | 7 | 2 | 14
> Mayo | 6 | 6 | 3 | 15
> ---------------------------|----
> | 23 | 27 | 10 | 50
>
>
> What is the closest that I can come to doing this in SQL?
>
>
> I have asked about this on a newsgroup that I frequent that deals with
> my db server of choice (Interbase Open Source) and have been told that I
> can do it in a Stored Proceedure, but even then I have to know in
> advance the number of entris in the Gender "table" for example.
>
>
> If it's not possible at the moment, is it not worth considering as
> functionality that should be part of a db server's capabilities?
>
> Do any servers do it at the moment?
>
>
> Any ideas, debates, rants, URLs, references welcome!
>
>
>
> Paul...
>
>
>
>
> --
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04
>
> p.s. just lacerated a tendon in left hand, so pls
> excuse typos and tricky abbrevs - TIA.
Received on Tue Aug 12 2003 - 04:23:51 CEST