Cumulative SUM SQL Server 2008

Question

I have table

Year Month Week Amount ------------------------------------- 2014 1 1 25501000,00 2014 1 2 118852000,00 2014 1 3 135764000,00 2014 1 4 153967000,00 2014 1 5 157648000,00

And I need select with one more row, that will be cumulative SUM of amount in each week:

Year Month Week ApvdAmtWeek SUMamount --------------------------------------------------- 2014 1 1 25501000,00 x1 2014 1 2 118852000,00 x1+x2 2014 1 3 135764000,00 x1+x2+x3 2014 1 4 153967000,00 x1+x2+x3+x4 2014 1 5 157648000,00 x1+x2+x3+x4+x5

Thank you for any help.

Jithin Shaji · Accepted Answer · 2014-08-12 12:05:50Z

This may help you.

DECLARE @TAB TABLE (YEAR INT, MONTH INT, WEEK INT,AMOUNT BIGINT) INSERT INTO @TAB VALUES (2014,1,1,2550100000) , (2014,1,2,11885200000), (2014,1,3,13576400000), (2014,1,4,15396700000), (2014,1,5,15764800000)

--Query:

SELECT Year,Month,Week,SUM(AMOUNT) ApvdAmtWeek FROM ( SELECT B.YEAR,B.MONTH,B.WEEK,A.AMOUNT FROM @TAB A,@TAB B WHERE A.WEEK <= B.WEEK) LU GROUP BY YEAR,MONTH,WEEK

--Result:

enter image description here

Alex K. · Accepted Answer · 2014-08-12 11:01:48Z

One way, not pretty as there is no ID:

;with T as ( select row_number() over (order by year, Month, Week) rn, Year, Month, Week, Amount from thetable ) select T.rn, T.Year, T.Month, T.Week, sum(T2.Amount) from T inner join (select rn, Year, Month, Week, Amount from T ) T2 on T.rn >= T2.rn group by T.rn, T.Year, T.Month, T.Week order by T.rn

The group by will not work correctly, becaouse of the "rn" column am i right...?
As rn is unique for every row no T grouping occurs but sum() still sums T2

Collectives™ on Stack Overflow

Cumulative SUM SQL Server 2008

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related