Link to home
Start Free TrialLog in
Avatar of IntercareSupport
IntercareSupport

asked on

Sum of Nested Count Query

I have a simple query I need help on.  If you look at the last item on my select statement, you'll see the attempt to get the total sum of the number of patients for the next row of results.

This is the line that is causing trouble: SUM(COUNT(Detail.Patient_Id) AS Count) AS Total

Currently it reports this: 'SUM' is not a recognized built-in function name.

I need the total number of patients, not separated by location.  Ideas?
SELECT DISTINCT Location.[Location Name] AS [Maternal Enrollments], COUNT(Detail.Patient_Id) AS Total, SUM(COUNT(Detail.Patient_Id) AS Count) AS Total
FROM         Detail INNER JOIN
                      Location ON Detail.LocationID = Location.LocationID
WHERE     (Detail.LocationID BETWEEN 60 AND 63)
GROUP BY Location.[Location Name], Detail.MIHP
HAVING      (Detail.MIHP = 1)

Open in new window

Avatar of dan_neal
dan_neal
Flag of United States of America image

You had some improper syntax.  See correction below.
Your command:
SUM(COUNT(Detail.Patient_Id) AS Count) AS Total
Should be:
SUM(COUNT(Detail.Patient_Id)) AS Total

SELECT DISTINCT Location.[Location Name] AS [Maternal Enrollments], COUNT(Detail.Patient_Id) AS Total, SUM(COUNT(Detail.Patient_Id)) AS Total
FROM         Detail INNER JOIN
                      Location ON Detail.LocationID = Location.LocationID
WHERE     (Detail.LocationID BETWEEN 60 AND 63)
GROUP BY Location.[Location Name], Detail.MIHP
HAVING      (Detail.MIHP = 1)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gallitin might be correct. Please check these exmples and decide what exactly you want. If you still don't get answer, post some sample set and your expected result.
create table #temp (col1 int, col2 int)
insert into #temp values (100,1),(100,1),(100,1),(100,1),(100,1),(100,1),(100,2),(100,2),(100,3),
                         (200,4),(200,4),(200,4),(200,4),(200,5),(200,6),(200,6)
select col1,COUNT(*) as cnt
  from #temp group by col1
  
select col1,COUNT(*) as cnt,count(col1) over () cnt2
  from #temp group by col1
 
col1	cnt	cnt2
100	9	2
200	7	2
 
select col1,COUNT(*) as cnt,(select COUNT(*) from #temp) cnt2
  from #temp group by col1
 
col1	cnt	cnt2
100	9	16
200	7	16
 
select col1,COUNT(*) as cnt,sum(col1) over () total
  from #temp group by col1
 
col1	cnt	total
100	9	300
200	7	300

Open in new window

Avatar of IntercareSupport
IntercareSupport

ASKER

Perfect, thanks!
Dan, your syntax didn't work for me.  Maybe because I'm using MS SQL Server?  Thanks anyways!