Sum of count records

Question

I am using sql server 2008 R2. I am having a table in database that contains data regarding patients that are of different status like Active, Completed, Inactive etc. I am asked to get the patients with "Active" status group by date.

I have created following query :

SELECT COUNT(*) AS TotalActivePatients, CONVERT(VARCHAR(10), CreatedDtUTC,111) AS CreatedDtUTC
FROM TablePatient 
WHERE MonitoringStatus LIKE 'Active' 
AND IsDeleted=0
GROUP BY CONVERT(VARCHAR(10), CreatedDtUTC,111)

and that gives me the result like below :

TotalActivePatients CreatedDtUTC
1                   2013/02/24
2                   2013/02/25
4                   2013/02/28
4                   2013/03/01
1                   2013/03/06
1                   2013/03/10
3                   2013/03/11
2                   2013/03/14
3                   2013/03/15
2                   2013/03/16

But, It is giving me the number of active patients on specific date. I want the sum of total active patients upto that specific date.

Any solution?

Thanks

So what is the desired result and what is the underlying sample data for the result above? — Tim Schmelter, Apr 09 '13 at 12:19
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Tim Rogers, Apr 09 '13 at 12:20
Look, in 2013/02/24 there is 1 patient active, on 2013/02/25 it shows there are 2 patients active, but the patient of previous record i.e 2013/02/24 is also in active status on that date, so the sum should be 3, but as I am applying group by, this is showing only 2 patients that are created on that specific date, are active. — Dev, Apr 09 '13 at 12:22
I didn't look at this very long......(thus why a comment and not an answer), but check my example here: http://granadacoder.wordpress.com/2009/08/13/cte-running-total-example/ — granadaCoder, Apr 09 '13 at 13:37
I just updated my example. Wordpress butchered my original formatting. It should work now, as is. — granadaCoder, Apr 09 '13 at 13:50

score 0 · Answer 1 · answered Apr 09 '13 at 14:19

0

This will give you the running total...

SELECT  DISTINCT COUNT(1) OVER (ORDER BY CONVERT(VARCHAR(10), CreatedDtUTC,111)), 
        CONVERT(VARCHAR(10), CreatedDtUTC,111) AS CreatedDtUTC
FROM    TablePatient 
WHERE   MonitoringStatus = 'Active' 
AND     IsDeleted = 0

answered Apr 09 '13 at 14:19

Eric J. Price

2,740
1
15
21

PS: A like without wildcards is an `=`, might as well use it for readability. – Eric J. Price Apr 09 '13 at 14:20

score 0 · Answer 2 · answered Nov 05 '13 at 15:57

The correct answer will be the following:

SELECT  DISTINCT
     CONVERT(VARCHAR(10), CreatedDtUTC,111) AS CreatedDtUTC,
     COUNT(1) OVER (PARTITION BY CONVERT(VARCHAR(10), CreatedDtUTC,111)), 

FROM    TablePatient 
WHERE   MonitoringStatus = 'Active' 
AND     IsDeleted = 0
ORDER BY 1

Additional info according to using OVER clause can be found here: http://technet.microsoft.com/en-us/library/ms189461.aspx

Sum of count records

2 Answers2