Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> sum function using decode

sum function using decode

From: Ken Chesak <datavector_at_hotmail.com>
Date: 16 Nov 2004 14:21:31 -0800
Message-ID: <3f2f39c4.0411161421.77686abb@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US