Re: SQL Question

You should be able to use decode to handle this as ( I do not have the decode spec in front of me so just take this with a grain of salt

select merchant, sum(decode(status='Created', 1, 0)) Created, sum(decode(status='Pending Acceptance',1,0)) Pending, sum(decode(status='Accepted',1,0)) Accepted from TableA, TableB where TableA.IdStr=TableB.IdStr group by merchant

In article <Pine.HPP.3.93.960813140951.10800D-100000_at_mailgate>, dyson_at_template.com says...
>
>
>
>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
>
>
Received on Tue Aug 20 1996 - 00:00:00 CEST