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

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

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

0
IntercareSupport
Asked:
IntercareSupport
1 Solution
 
dan_nealCommented:
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

0
 
Nathan RileyFounder/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)

Open in new window

0
 
SharathData 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

Open in new window

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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