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
|
See
ReplyDeleteCalculating Values within a Rolling Window in Transact SQL https://www.simple-talk.com/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/ for some alternative ways of doing it that are faster.
Careful now. This is known as a "Triangular Join" and it's one of the classic methods to totally paralyze your server and drive I/O right into the stops. Please see the following article that explains the doom that can be caused by Triangular Joins.
ReplyDeletehttp://www.sqlservercentral.com/articles/T-SQL/61539/
Careful now. This is known as a "Triangular Join" and it's one of the classic methods to totally paralyze your server and drive I/O right into the stops. Please see the following article that explains the doom that can be caused by Triangular Joins.
ReplyDeletehttp://www.sqlservercentral.com/articles/T-SQL/61539/
I used another way to do this and got the same result:
ReplyDeleteselect ID,val,sum(val)
over ( order by ID
rows unbounded preceding) as cumSum
from testsum
This is the way I did it which produced same results:
ReplyDeleteselect ID,val,sum(val)
over ( order by ID
rows unbounded preceding) as cumSum
from testsum
Not working in SQL 2005
DeleteNot working in SQL 2005
Deleterows unbounded preceding is a new feature of SQL SERVER 2012.It will not work in other versions of SQL Server.
DeleteAny comments on the following lines of code for calculating cumulative sum?
ReplyDeleteSELECT A2.*, B2.CumSum
FROM testsum A2
CROSS APPLY (
SELECT SUM(A1.val) AS CumSum
FROM testsum A1
WHERE A1.ID <= A2.ID
) B2