T-SQL iterative current sum by column value

Question

I'm working at SQL Server 2008 R2. I'm trying to write a stored procedure which will create new column with current sum of Costs.

I have MyTable:

ID     |   Costs
----------------
1      |     5
2      |     3
3      |     2
4      |     4

but I need the third column 'CurrentCosts' with values:

ID     |   Costs   |  CurrentCosts
----------------------------------
1      |     5     |      5
2      |     3     |      8
3      |     2     |      10
4      |     4     |      14

The first value in 'CurrentCosts' is: 5 + 0 = 5
The second value in 'CurrentCosts' is: 5 + 3 = 8
The third value in 'CurrentCosts' is: 8 + 2 = 10
The fourth value in 'CurrentCosts' is: 10 + 4 = 14

and so on.

I tried with:

declare @ID INT
declare @current_cost int
declare @running_cost int

select @ID = min( ID ) from MyTable
set @running_cost = 0
set @current_cost = 0

while @ID is not null
begin
    select ID, Costs, @running_cost as 'CurrentCosts' from MyTable where ID = @ID
    select @ID = min( ID ) from MyTable where ID > @ID
    select @current_cost = Costs from MyTable where ID = @ID
    set @running_cost += @current_cost
end

It works but if anybody have better solution, I will be grateful. I got numerous tables with just one result in each and as much tables as I have SELECT commanad in loop. Is there some solution where I will get just one table with all results.

Do you want to run this procedure once for the complete table or every time a new row is added? If the latter, try `Select @running_cost =SUM(costs) from MyTable`. — LuigiEdlCarno, Jul 22 '13 at 12:11
possible duplicate of [how to get cumulative sum](http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — bummi, Jul 22 '13 at 12:13

score 4 · Accepted Answer · answered Jul 22 '13 at 12:11

You could use a sub query:

SELECT ID, Costs, 
       (SELECT Sum(Costs) 
        FROM   dbo.MyTable t2 
        WHERE  t2.ID <= t1.ID) AS CurrentCosts 
FROM   dbo.MyTable t1

Demo

ID     COSTS    CURRENTCOSTS
1        5            5
2        3            8
3        2            10
4        4            14

score 0 · Answer 2 · answered Jul 22 '13 at 12:37

0

You can find this interesting http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

answered Jul 22 '13 at 12:37

Madhivanan

13,470
1
24
29

T-SQL iterative current sum by column value

2 Answers2