Home » SQL & PL/SQL » SQL & PL/SQL » COUNT funtion help
COUNT funtion help [message #206068] Tue, 28 November 2006 09:14 Go to next message
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This line here
COUNT(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 Go to previous message
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
Previous Topic: Help! SQL TUNING!
Next Topic: Comparison
Goto Forum:
  


Current Time: Mon Feb 17 22:27:51 CST 2025