Previous row value added to the current row and so on SQL Server

Question

I am using version 2012.

I have this table with the data.

        create table #temp_metrics
        (
            id int identity(1,1)
            ,prev_total int not null default(0)
            ,added int not null default(0)
            ,total int not null default(0)
        )

        insert into #temp_metrics
        (
             added
        )
        values
        (
            10
        ),(20),(5),(15),(9)

        select * from #temp_metrics

I am expecting the output as below

Previous row total should be current row prev_total

Total should be the previous row total+added

I am using the below query

      ;WITH CTE AS (
                    SELECT
                        id
                        ,Total
                    from 
                        #temp_metrics
                    )
        SELECT
                prev.total  as prev_total 
                ,t.added
                ,prev.total +t.added  as total
        FROM 
            #temp_metrics t
            LEFT JOIN CTE prev ON prev.id = t.id - 1

But looks like I am missing something.

How can I get the output as in the image format ?

Thanks in advance.

Possible duplicate of [http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Wagner DosAnjos, Nov 15 '16 at 15:47

score 1 · Answer 1 · answered Nov 15 '16 at 15:53

1

You could try

Select ID
      ,Prev_Total = sum(Added) over (Order By ID) -Added
      ,Added
      ,totoal = sum(Added) over (Order By ID)
 From  #temp_metrics 
 Order By ID

Returns

ID  Prev_Total  Added   totoal
1   0           10      10
2   10          20      30
3   30          5       35
4   35          15      50
5   50          9       59

answered Nov 15 '16 at 15:53

John Cappelletti

79,615
7
44
66

1

This answer would be useful to future readers if it had some explanation of why it worked. I had to do quite a bit of research to find out why it wasn't producing the same "totoal" for every row. – Tab Alleman Nov 15 '16 at 19:31

score 0 · Answer 2 · answered Nov 15 '16 at 15:55

0

You can do this using sum and lag

;with cte as (
select *, RunningTotal = sum(added) over(order by id) from #temp_metrics 
)
select *, lag(RunningTotal,1, 0) over(order by id) as Prev_total from cte

answered Nov 15 '16 at 15:55

Kannan Kandasamy

13,405
3
25
38

Previous row value added to the current row and so on SQL Server

2 Answers2