Creating a cumulative sum column

Question

Sample table ID: (num is a key so there wouldn't be any duplicates)

I need a query to make this: Desired output: (Should be sorted and have a cumulative sum column)

num cumulative
1    1
2    3
3    6
5    11
6    17
8    25

In Mysql I have

Select num as n, 
       (select sum(num) from ID where num <= n)
from ID order by n;

I picked up this example in this link but in MySqlServer

Then there will be no exact decision without proper identifying column — Oleg Dok, Dec 22 '11 at 14:15
You find a solution here: https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — flobrr, Oct 17 '17 at 08:59

score 3 · Answer 1 · answered Dec 22 '11 at 14:20

3

Try this:

SELECT num,(SELECT SUM(num) FROM tab WHERE num<=t.num) AS CumulativeSum
FROM tab t

answered Dec 22 '11 at 14:20

Rahul

76,197
13
71
125

score 0 · Answer 2 · answered Dec 22 '11 at 14:28

Since num is not unique, you may need to use row_number to get a unique number in there, then do the sum:

declare @t as table (num int)

insert into @t (num) values (1)
insert into @t (num) values (5)
insert into @t (num) values (6)
insert into @t (num) values (7)
insert into @t (num) values (2)
insert into @t (num) values (3)

;with rows as (
    select
        num,
        ROW_NUMBER() over (order by num) as Row
    from @t
)
select num, (select sum(num) from rows where row <= r.row)
from rows r

Creating a cumulative sum column

2 Answers2