Re: SQL Question

From: Beth J. Katcher <katcher_at_a1.tch.harvard.edu>
Date: 1996/08/14
Message-ID: <3211C956.769D_at_a1.tch.harvard.edu>#1/1

Heather Dyson wrote:
>
> Let's say I have the following two tables
>
> TableA
> ------
> IdStr
> merchant
>
> TableB
> ------
> IdStr
> Seq
> status
>
> (IdStr) is the primary key of TableA. (IdStr, Seq) is the primary key of
> TableB. I need to have output that looks like the following
>
> Merchant Created Pending Acceptance Accepted
> -------- ------- ------------------ --------
>
> This would have the merchant and counts of all the entries in TableB that
> have a certain status.
>
> If it was just a count of one type of status the query would be:
>
> select TableA.merchant,
> count(TableB.status)
> from TableA,
> TableB
> where TableA.IdStr = TableB.IdStr and
> TableB.status = 'Created'
> group by TableA.merchant;
>
> My question is how do I get a serious of counts into one select statement
> so that I can count all the statuses for one merchant?
>
> Thanks,
> Heather

Heather, the whole trick to what you want to do is in the DECODE:

select TableA.merchant,

       COUNT(DECODE(TableB.status, 'CREATED', 'X', null)) created_cnt,
       COUNT(DECODE(TableB.status, 'PENDING', 'X', null)) pending_cnt,
       COUNT(DECODE(TableB.status, 'ACCEPTED', 'X', null)) accepted_cnt 

from TableA, TableB
where TableA.IdStr = TableB.IdStr and

      TableB.status = 'Created'
group by TableA.merchant;

Beth Received on Wed Aug 14 1996 - 00:00:00 CEST