| 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,name
Received on Tue Nov 16 2004 - 16:21:31 CST
![]() |
![]() |