Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> sum function using decode
The following sql picks unique resident_id by the greatest date which
is column a3a by facility. I count the total residents and then there
is a second count which is only residents with q1a = 1. That is done
with sum(decode(xa1a,1,1,0)). The total count is fine, but the q1a
count is right sometimes but mostly wrong, usally short by 1 or 2.
Any ideas? Oracle9i Enterprise Edition Release 9.2.0.2.1.
select xfacid, name, address, fac_city,
fac_st, fac_zip, ltrim(state_rgn_cd,'L'), cnt, q1acnt from facility f, ( select facility_internal_id xfacid, count(*) cnt, sum(decode(xq1a,1,1,0)) q1acnt from ( select facility_internal_id, resident_internal_id, q1a xq1a, row_number() over (partition by resident_internal_id order by a3a desc) rn from assessment a, section_a b, section_aa aa, section_q q where b.a3a >= sysdate-120 and a.aa8a between '01' and '05' and a.assessment_internal_id = b.assessment_internal_id and b.assessment_internal_id = q.assessment_internal_id and b.assessment_internal_id = aa.assessment_internal_id ) where rn = 1 group by facility_internal_id) where xfacid = f.facility_internal_id order by state_rgn_cd,nameReceived on Tue Nov 16 2004 - 16:21:31 CST
![]() |
![]() |