From: sato@cslab.co.jp (Toshikatsu.Sato)
Subject: Re: Need help with a SQL query
Date: 1998/04/17
Message-ID: <6h6ler$g2b@Mercury.spr.cslab.co.jp>#1/1
References: <6h2t16$ajm$1@nnrp1.dejanews.com>
Mime-Version: 1.0
Reply-To: tosikatu@mxc.meshnet.or.jp
Content-Type: Text/Plain; charset=US-ASCII
Organization: C'sLab.,Ltd
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools



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@cslab.co.jp