Re: horizontal group by?

"Gordon" <gordon_t_wu_at_hotmail.com> a écrit dans le message de news:IRYEc.553$Ny6.1441_at_mencken.net.nih.gov...
>
> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> news:40e42b10$0$24429$636a15ce_at_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
> >
> >
>
> I'm sorry, group.
> I've made a typo.
> I really need to group the field hoizontally by fB<x (not fB=x).
> I didn't think its possible
>
>

Just slighty modify the query:

select yearA, count(*),
sum(decode(sign(fB-x),-1,1,0)) "fB<x",
sum(decode(fB,y,1,0)) "fB=y",
from customer
group by yearA
/

--
Regards
Michel Cadot