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_I d) 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?
This is the line that is causing trouble: SUM(COUNT(Detail.Patient_I
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Perfect, thanks!
ASKER
Dan, your syntax didn't work for me. Maybe because I'm using MS SQL Server? Thanks anyways!
Your command:
SUM(COUNT(Detail.Patient_I
Should be:
SUM(COUNT(Detail.Patient_I
Open in new window