Re: SQL Question

From: <bwskiles_at_adpc.purdue.edu>
Date: 1996/08/14
Message-ID: <NEWTNews.30753.840036339.Postmaster_at_freh25-87.adpc.purdue.edu>#1/1

> 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, here's another version of the one posted by Beth. It uses SUM instead of COUNT. (Both certainly work, however.)

select TableA.merchant,

       SUM(DECODE(TableB.status, 'CREATED', 1, 0)) created_cnt,
       SUM(DECODE(TableB.status, 'PENDING', 1, 0)) pending_cnt,
       SUM(DECODE(TableB.status, 'ACCEPTED',1, 0)) accepted_cnt 

from TableA, TableB
where TableA.IdStr = TableB.IdStr
group by TableA.merchant; Received on Wed Aug 14 1996 - 00:00:00 CEST