Accumulating in SQL

Question

I have a query with results like ID, Value. What I want is to get the values in order of their ids and also calculate the accumulated value in another column. take a look at my simplified code:

declare @TempTable Table
(
    ID int,
    Value int
)
insert into @TempTable values
(1, 10),
(2, -15),
(3, 12),
(4, 18),
(5, 5)

select t1.ID, t1.Value, SUM(t2.Value) AccValue from @TempTable t1
inner join @TempTable t2 on t1.ID >= t2.ID
group by t1.ID, t1.Value
order by t1.ID




Result:
ID  Value   AccValue
1   10      10
2   -15     -5
3   12      7
4   18      25
5   5       30

What I have come up with, is to use inner join between the result and itself for that purpose. But for huge amount of data, it's clearly a low performance issue.

Is there any other alternative to do that?

Answer 1

In 2012 version, you can use:

SELECT
    id,
    Value,
    AccValue = SUM(Value) OVER (ORDER BY ID 
                                ROWS BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW)
FROM 
    @TempTable ;

For previous versions of SQL-Server, see my answer in this similar question: Recursive SQL- How can I get this table with a running total?, with a cursor solution.

Even better, follow the link to the great article by @Aaron Bertrand, that has a thorough test of various methods to calculate a running total: Best approaches for running totals – updated for SQL Server 2012