Hello all,
Oracle 7.3.3 on NT
I'm looking for a pure SQL method of counting grouped records even if there
are no instances to count.
My current SQL:
- SQL Begin --
- Active employee count by deptid
select substr(j.deptid,0,2) as Groups
, decode(substr(j.sal_admin_plan,0,1),'R', 'Rep','NonRep') as Rep_NonRep
, count(j.emplid) as Total
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),
decode(substr(j.sal_admin_plan,0,1),'R', 'Rep','NonRep')
order by substr(j.deptid,0,2),
decode(substr(j.sal_admin_plan,0,1),'R', 'Rep','NonRep') desc;
- END SQL
This gives counts of all instnaces of records. However, if there are
departments with 'Rep' employees but not 'Non-Rep' employees, this needs to
be indicated with a '0' (i.e.<group>, NonRep, 0). I have tried decoding the
'count(j.emplid)' record looking for zero, but was not successful.
Any ideas?
Thanks,
Mark
Received on Mon Mar 11 2002 - 18:45:44 CST
