Re: horizontal group by?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 1 Jul 2004 17:18:54 +0200
Message-ID: <40e42b10$0$24429$636a15ce@news.free.fr>

"Gordon" <gordon_t_wu_at_hotmail.com> a écrit dans le message de news:EBVEc.547$Ny6.1219_at_mencken.net.nih.gov...
> Hi Group,
>
> I'm wondering if its possible to generate the following report with a
> single SQL statement:
>
> The original report is easy, it looks like:
>
> Year A| new_cust
> ------|------------
> 1990 | 3000
> 1991 | 4000
> 1992 | 5000
>
>
> All I needed to do here is:
> SELECT count(cust_id), yearA
> FROM customer
> Group by yearA
>
> Now the requirement gets interesting.., they want me to to split the
> new_cust horizontally based on another field (say, fB). So the
> report looks like:
>
> Year A | new_cust | new_cust when fB=x | new_cust when fB=y
> --------|----------------------------------|---------------------
> 1990 | 3000 | 1000 | 2000
> 1991 | 4000 | 2000 | 2000
> 1992 | 5000 | 4000 | 1000
>
> Its almost seems like I got to somehow perform another group-by
> horizontally.
> How should I generate this report in a single? Is it possible?
>
> Thanks
>
>

If i correctly understand you, try something like:

select yearA, count(*),
sum(decode(fB,'x',1,0)) "fB=x",
sum(decode(fB,'y',1,0)) "fB=y",

from customer
group by yearA
/

--
Regards
Michel Cadot

Received on Thu Jul 01 2004 - 10:18:54 CDT