CODEWITHSUNDEEP
sqlsql-serversql-server-2008select
guitarzero
guitarzero

Reputation: 585

Accumulate rows in SQL: summarizing previous values and new grouped by date

In SQL Server 2008, If I have a table like this.

Date          NoOfCases
--------------------
2013-11-27    1
2013-11-28    2
2013-11-29    1
2013-11-30    3

And I want the previous sum to be added to the next row's value. That means that the output should be like this:

Date          AccumulatedNoOfCases
2013-11-27    1
2013-11-28    3
2013-11-29    4
2013-11-30    7

Any suggestions? Thanks in advance.

Upvotes: 3

Views: 5253

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You could do a join on same table, with the date being smaller or equal.

select t1.date, sum(t2.NoOfCases) as AccumulatedNoOfCases
from Table1 t1
join Table1 t2 on t2.Date <= t1.Date
group by t1.date

SqlFiddle

With count, i would use a cte for simplicity

with cnt as (select date, count(*) as cnt from Table1
             group by date)

select t1.date, sum(t2.cnt) as AccumulateNoOfCases
FROM cnt t1
join cnt t2 on t2.date <=t1.date
group by t1.date;

see SqlFiddle

With your datas, it should be something like that.

WITH cte as
(select [Date of hospital visit]) as dte, count(*) as cnt
 FROM DW_Epidemic_Warning
 GROUP BY [Date of hospital visit])

select t1.dte, sum(t2.cnt) as AccumulateNoOfCases
FROM cte t1
join cte t2 on t2.dte <=t1.dte
group by t1.dte;

Upvotes: 4

AllenG
AllenG

Reputation: 8190

I like this one:

Select c.CaseDate,
  (Select Sum(NoOfCases) 
   From Cases
   Where CaseDate <= c.CaseDate
   AND CaseDate > '2013-11-20') AS AccumulatedCases
From Cases c

Obviously that AND CaseDate > '2013-11-20' is completely arbitrary in this case; you'd probably want to use a parameter there.

SqlFiddle

Upvotes: 0

dazedandconfused
dazedandconfused

Reputation: 3186

You didn't say what version of SQL Server you are using. If it is 2012, you can do something like 

SELECT 
  t1.date, sum(t1.NoOfCases) OVER (ORDER BY t1.date) AS AccumulatedNoOfCases
FROM Table1 t1

Upvotes: 3

Related Questions