COUNT funtion help [message #206068] |
Tue, 28 November 2006 09:14  |
staann56
Messages: 136 Registered: May 2006 Location: atlanta
|
Senior Member |
|
|
I'm trying to pull all patients with a diagnosis of Influenza and count the different age groups.
My counts aren't correct. If I run the subquery by 'itself' I get a total of 5 people but when ran
with the outer query the ILI_Total_Admit_SQL2 is 3 and the age breakdown totals are 1 for Age4_SQL2,
1 for Age6_SQL2, and 2 for Age7_SQL2. Any ideas?
Any help would be greatly appreciated.
Thanks,
Stan
SELECT DISTINCT
COUNT(DISTINCT(p.ps)) OVER() Total_Admits_SQL2,
COUNT(DISTINCT(ILI_Total_Admit_SQL2.ps)) OVER() ILI_Total_Admit_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 0 AND 1
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age1_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 2 AND 4
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age2_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 5 AND 18
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age3_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 19 AND 34
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age4_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 35 AND 49
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age5_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age BETWEEN 50 AND 64
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age6_SQL2,
COUNT(CASE
WHEN ILI_Total_Admit_SQL2.Age > 65
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age7_SQL2,
COUNT (CASE
WHEN ILI_Total_Admit_SQL2.Age IS NULL
THEN ILI_Total_Admit_SQL2.Age
END) OVER() Age8_SQL2
FROM
pat p,
(SELECT
pd.ps,
TRUNC((sysdate - p.bth_dt) / 365.25) Age
FROM
pat p,
patdgn pd
WHERE
p.ps = pd.ps(+)
AND (TO_CHAR(p.dt,'MM/DD/YYYY HH24:MI') BETWEEN TO_CHAR(TRUNC(sysdate-1),'MM/DD/YYYY HH24:MI') AND TO_CHAR(TRUNC((sysdate-1)+((23/24)+(59/1440))),'MM/DD/YYYY HH24:MI'))
AND pd.diag_desc IN ('INFLUENZA'))
AND p.fac = ('Z')) ILI_Total_Admit_SQL2
WHERE
p.pat_seq = ILI_Total_Admit_SQL2.ps(+)
AND (TO_CHAR(p.admit_dt,'MM/DD/YYYY HH24:MI') BETWEEN TO_CHAR(TRUNC(sysdate-1),'MM/DD/YYYY HH24:MI') AND TO_CHAR(TRUNC((sysdate-1)+((23/24)+(59/1440))),'MM/DD/YYYY HH24:MI'))
AND p.fac IN ('Z')
AND p.dept IS NOT NULL
|
|
|
Re: COUNT funtion help [message #206070 is a reply to message #206068] |
Tue, 28 November 2006 09:26   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This line hereCOUNT(DISTINCT(p.ps)) OVER() will return the number of distinct PS values - is it possible there's a duplicate, as that would make the total look smaller than the sum of the ranges.
|
|
|
Re: COUNT funtion help [message #206077 is a reply to message #206068] |
Tue, 28 November 2006 10:27  |
staann56
Messages: 136 Registered: May 2006 Location: atlanta
|
Senior Member |
|
|
Thanks. I'll use DISTINCT with SELECT (SELECT DISTINCT) instead of in my COUNT function. That should take care of any dups.
Thanks,
Stan
|
|
|