Sometimes we need to find sum of first and next row in cumulative way.
Create table and insert data:
CREATE TABLE [dbo].[testsum](
[name] [varchar](10) NULL,
[val] [int] NULL,
[ID] [int] NULL
) ON [PRIMARY]
insert into [testsum] (id,name,val)
values(1,'A',10),
(2,'B',20),
(3,'C',30)
Required Output:
ID name val cumSum
1 A 10 10
2 B 20 30
3 C 30 60
To find cumulative sum first you need to self join on condition >=
select t1.*,t2.* from testsum t1 inner join testsum t2 on t1.ID>=t2.ID
output after join.
t1 | t2 | ||||
ID | name | val | ID | name | val |
1 | A | 10 | 1 | A | 10 |
2 | B | 20 | 1 | A | 10 |
3 | C | 30 | 1 | A | 10 |
2 | B | 20 | 2 | B | 20 |
3 | C | 30 | 2 | B | 20 |
3 | C | 30 | 3 | C | 30 |
Group by ID and SUM.
select t1.id, t1.val, SUM(t2.val) as cumSum from testsum t1 inner join testsum t2 on t1.id >= t2.id group by t1.id, t1.val order by t1.id
t1 | t2 | ||||
ID | name | val | ID | name | val |
1 | A | 10 | 1 | A | 10 |
2 | B | 20 | 1 | A | 10 |
3 | C | 30 | 1 | A | 10 |
2 | B | 20 | 2 | B | 20 |
3 | C | 30 | 2 | B | 20 |
3 | C | 30 | 3 | C | 30 |
We reach to output:
id | val | cumSum |
1 | 10 | 10 |
2 | 20 | 30 |
3 | 30 | 60 |
