How to do running sum using CTE - SQL Server

Many time we need to do runing sum of value.
In SQL 2oo5 we can do it with use of CTE



We have table which have monthwise data for 2 years. We want running sum monthwise , which needs to reset on start of year


create table #t

(month int ,

year int,

value int)

insert into #T values (1,2001,10)

insert into #T values (2,2001,20)

insert into #T values (3,2001,30)

insert into #T values (4,2001,40)

insert into #T values (5,2001,50)

insert into #T values (6,2001,60)

insert into #T values (7,2001,70)

insert into #T values (8,2001,80)

insert into #T values (9,2001,90)

insert into #T values (10,2001,100)

insert into #T values (11,2001,110)

insert into #T values (12,2001,120)

insert into #T values (1,2002,5)

insert into #T values (2,2002,15)

insert into #T values (3,2002,25)

insert into #T values (4,2002,35)

insert into #T values (5,2002,45)

insert into #T values (6,2002,55)

insert into #T values (7,2002,65)

insert into #T values (8,2002,75)

insert into #T values (9,2002,85)

insert into #T values (10,2002,95)

insert into #T values (11,2002,105)

insert into #T values (12,2002,115)
go
with test

as

(select * , value as running_sum from #t where month = 1

union all

select t.*, t.value + t1.running_sum from #t t inner join test t1

on t.month = t1.month+1 and t.year = t1.year where t.month > 1

)

select * from test

order by year,month

option (maxrecursion 0)






Here we can see it do running sum monthwise , and it resets as start of year.

Now if we want running sum for all data instead of reset it at start of year
we need to generate rownumber for all rows in the table.

WITH rowtest
     AS (SELECT *,
                Row_number() OVER (ORDER BY year, month) rownum
         FROM   #t),
     test
     AS (SELECT *,
                value AS running_sum
         FROM   rowtest
         WHERE  rownum = 1
         UNION ALL
         SELECT t.*,
                t.value + t1.running_sum
         FROM   rowtest t
                INNER JOIN test t1
                    ON t.rownum = t1.rownum + 1
         WHERE  t.rownum &> 1)
SELECT *
FROM   test
ORDER  BY rownum
OPTION (maxrecursion 0)







3 comments:

Alex said...

Wonderful stuff, it solved my problem, kudos

Gustavo Lopez said...

Thank you for this nice example that works in SQL.

Vintage Freak said...

What is ...

WHERE t.rownum &> 1

shouldn't it be simply ...

WHERE t.rownum > 1

Post a Comment

Popular Posts