Adding a running total column in SQL Server

Question

I have a result set from a SQL Server query as below:

Item Week Amount

1     1      500
1     2     -100
1     3     -200
2     1      800
2     2     -600
2     3     -800

What I want to know is, is it possible to derive a fourth column that gives a running total of amount for each item?

My desired result is as follows:

Item Week Amount Total

1     1      500  500
1     2     -100  400
1     3     -200  200
2     1      800  800
2     2     -600  200 
2     3     -800 -600

I am using SQL Server 2008

Thanks, i have the same problem.

Hong Van Vit
– Hong Van Vit

2017-10-16 02:58:26 +00:00
Commented Oct 16, 2017 at 2:58 — Hong Van Vit
– Hong Van Vit, Commented Oct 16, 2017 at 2:58
I hope you saw the answer below!

Harry
– Harry

2017-10-16 03:10:06 +00:00
Commented Oct 16, 2017 at 3:10 — Harry
– Harry, Commented Oct 16, 2017 at 3:10

Juan Carlos Oropeza · Accepted Answer · 2015-12-09 23:34:04Z

4

try SUM() OVER

SELECT  
      item, 
      week, 
      amount,
      SUM(amount) over (partition by item order by Week) as Total
FROM yourTable

answered Dec 9, 2015 at 23:34

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harry Over a year ago

Awesome.. thank you so much. Only thing I had to do was SUM(Amount) over (partition by item order by Week) as Total

Dennis C · Accepted Answer · 2015-12-10 06:22:53Z

2

You will need a SQL Server 2012 to support ROWS PRECEDING functions.

SELECT  
  item, 
  week, 
  amount,
  SUM(amount) over (partition by item order by Week
       ROWS UNBOUNDED PRECEDING
  ) as Total
FROM yourTable

http://stevestedman.com/2012/03/rows-preceding-and-following-in-tsql-2012/

answered Dec 10, 2015 at 6:22

Dennis C

24.8k13 gold badges75 silver badges100 bronze badges

1 Comment

Harry Over a year ago

This code works just fine thanks.. but there seems to be a performance degradation when I use the Rows Unbounded Preceding. My query that runs in about 4 seconds is dragged out to 25 seconds.

Collectives™ on Stack Overflow

Adding a running total column in SQL Server

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related