Re: counting zeros

From: DStevens <dstevens_at_navidec.com>
Date: Tue, 12 Mar 2002 08:28:37 -0700
Message-ID: <a6l6r5$r2i$1@newsreader.mailgate.org>

Just a quick glance, so it may not work. Basically moving the rep and nonrep into a column by group:

select substr(j.deptid,0,2) as Groups,
sum(decode(substr(j.sal_admin_plan,0,1),'R',1,0)) RepCount, sum(decode(substr(j.sal_admin_plan,0,1),'R',0,1)) NonRepCount  from ps_job j
where j.empl_rcd_nbr = '0'
  and j.effdt = (SELECT MAX(j1.effdt)

     FROM ps_job j1
    WHERE j1.emplid = j.emplid
  AND j1.effdt <= '1-APR-2001')
  AND j.effseq = (SELECT MAX(j1.effseq)

          FROM ps_job j1
         WHERE j1.emplid = j.emplid
           AND j1.effdt = j.effdt)

  and j.effdt < '1-APR-2001'
  and j.action not in ('TER','RET')
Group by substr(j.deptid,0,2)

Might have to wrap the sum() in an nvl to translate nulls to 0 - should work! Received on Tue Mar 12 2002 - 09:28:37 CST