Friday, February 12, 2016

Cumulative sum in SQL

This is one of the possibilities to calculate cumulative sum in SQL.
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
Result
 ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |
When grouping is required, it is better to use this solution (only SQL2012 and later)
select 
 cte.EPICSystem_Title
 ,cte.Uren
 ,cte.Realisation
 ,cte.Datum
 ,cte.Budget
    ,sum(cte.uren) over (partition by cte.EPICSystem_Title order by datum) as CumUren
 ,sum(cte.Realisation) over (partition by cte.EPICSystem_Title order by datum) as CumRealisation
from cte