How to get cumulative sum in SQL Server (Z=Z+A+B-C)

Question

I want to create cumulative sum in table ( Commul_table) in SQL such as Z=Z+ (A+B-C )

Commul_table i want compute value of Z

Does this answer your question? https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — Ziba, Aug 20 '20 at 20:34
no , because i want to add and subtract more than one cell in each row in column Z , (Z=Z+A+B-C) — Ali Jamal, Aug 20 '20 at 20:40
The sample data seems to have nothing to do with the calculations you are suggesting. The second `z` value should be 8. — Gordon Linoff, Aug 20 '20 at 21:44

Olga Romantsova · Accepted Answer · 2020-08-20T22:03:49.033

2

You can use analytic function in this case with rows unbounded preceding (to get sum values before current row)

Select id, A,B,C,sum(A+B-C) over(order by Id rows unbounded preceding) as Z
From Table

edited Aug 20 '20 at 22:03

answered Aug 20 '20 at 21:03

Olga Romantsova

1,096
1
4
8

Ali Jamal, do you try query?) – Olga Romantsova Aug 21 '20 at 11:34
Thanks for the help, it's run. – Ali Jamal Aug 21 '20 at 19:54
If it is run, please check answer - just mark it as solution) – Olga Romantsova Aug 22 '20 at 06:45

score 1 · Answer 2 · answered Aug 20 '20 at 21:21

1

It's still a Cumulative Sum, just based on a calculation on three columns:

sum(a+b-c) over (order by Id rows unbounded preceding)

See fiddle

answered Aug 20 '20 at 21:21

dnoeth

57,618
3
33
50

thinks for help – Ali Jamal Aug 22 '20 at 05:36

score 1 · Answer 3 · answered Aug 20 '20 at 21:47

I'm answering to point out that the window frame clause is not needed in most databases. Assuming that the id is unique:

select ct.*, sum(a + b - c) over (order by id) as z
from Commul_table;

If id can be duplicated, please explain what you want for the results. The above also assumes that a, b, and c are never NULL. If they can be, then you want to substitute 0 for them:

select ct.*,
       sum(coalesce(a, 0) + coalesce(b, 0) - coalesce(c, 0)) over (order by id) as z
from Commul_table;

How to get cumulative sum in SQL Server (Z=Z+A+B-C)

3 Answers3