[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1447

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)
``````
0
IntercareSupport
1 Solution

Commented:
You had some improper syntax.  See correction below.
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)
``````
0

Founder/CTOCommented:
How do you sum something that you already have counted?  I think you want the total number of Distinct Patient_IDs?  Try this:
``````SELECT DISTINCT Location.[Location Name] AS [Maternal Enrollments], COUNT(Detail.Patient_Id) AS Total, 'ALLPATIENTS'= (select distinct COUNT(D1.Patient_Id)
FROM Detail D1)
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)
``````
0

Data EngineerCommented:
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
``````
0

Author Commented:
Perfect, thanks!
0

Author Commented:
Dan, your syntax didn't work for me.  Maybe because I'm using MS SQL Server?  Thanks anyways!
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.