Re: Need help with a SQL query

From: Toshikatsu.Sato <sato_at_cslab.co.jp>
Date: 1998/04/17
Message-ID: <6h6ler$g2b@Mercury.spr.cslab.co.jp>#1/1

Hi,

select a.proj_id,

       decode(sum(a.cntFTP),  0, NULL, sum(a.cntFTP))  cntFTP,
       decode(sum(a.cntFTT),  0, NULL, sum(a.cntFTT))  cntFTT,
       decode(sum(a.cntPTP),  0, NULL, sum(a.cntPTP))  cntPTP,
       decode(sum(a.cntPTT),  0, NULL, sum(a.cntPTT))  cntPTT,
       decode(sum(a.cntLOA),  0, NULL, sum(a.cntLOA))  cntLOA,
       decode(sum(a.cntTERM), 0, NULL, sum(a.cntTERM)) cntTERM,
       sum(a.total)                                    total

  from
  (
    select proj_id,

           sum(decode(status, 'F/T/P', 1, 0)) cntFTP,
           sum(decode(status, 'F/T/T', 1, 0)) cntFTT,
           sum(decode(status, 'P/T/P', 1, 0)) cntPTP,
           sum(decode(status, 'P/T/T', 1, 0)) cntPTT,
           sum(decode(status, 'LOA',   1, 0)) cntLOA,
           sum(decode(status, 'TERM',  1, 0)) cntTERM,
           count(proj_id)                     total
      from sato
     group by proj_id, status

  ) a
 group by a.proj_id;

Result OK!!

---

 Toshikatsu.Sato(JAPAN) sato_at_cslab.co.jp Received on Fri Apr 17 1998 - 00:00:00 CDT