Re: SQL Question

From: <Lance.Humpert_at_mail.tju.edu>
Date: 1996/08/16
Message-ID: <177E5BFF3.ASVLH_at_TJUVM.TJU.EDU>#1/1

Heather Dyson has the following two tables:  

> TableA
> ------
> IdStr
> merchant
>
> TableB
> ------
> IdStr
> Seq
> status
 

 (snip)  

And she needs to have her output look 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.
 

 (snip)  

> 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?
 

Can you create views? Though it may not be the best solution, you could create three separate views which would each contain the required counts:  

  create view crea_data (IdStr, crea_count) as

         select TableB.IdStr, count(TableB.Seq)
           from TableB
          where TableB.status = 'Created'
          group by TableB.IdStr;
  create view pend_data (IdStr, pend_count) as
         select TableB.IdStr, count(TableB.Seq)
           from TableB
          where TableB.status = 'Pending'
          group by TableB.IdStr;
  create view accp_data (IdStr, accp_count) as
         select TableB.IdStr, count(TableB.Seq)
           from TableB
          where TableB.status = 'Accepted'
          group by TableB.IdStr;
 

With that done, the query is almost trivial:  

  select TableA.merchant Merchant,
         nvl(crea_data.crea_count, 0) Created,
         nvl(pend_data.pend_count, 0) 'Pending Acceptance'
         nvl(accp_data.accp_count, 0) Accepted

    from crea_data, pend_data, accp_data, TableA

   where TableA.IdStr = crea_data.IdStr(+)
     and TableA.IdStr = pend_data.IdStr(+)
     and TableA.IdStr = accp_data.IdStr(+);
 

That should do it. A 'group by TableA.merchant' should not be necesary if TableA has no duplicate merchants, but you may want to add your own 'order by' clause. Note that the outer joins - (+) - are required or only those merchants with all three statuses will be reported.  

• Lance (see your dba for 'create view' privileges...)

Received on Fri Aug 16 1996 - 00:00:00 CEST