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?

possible duplicate of [Recursive SQL- How can I get this table with a running total?](http://stackoverflow.com/questions/17050660/recursive-sql-how-can-i-get-this-table-with-a-running-total) — ypercubeᵀᴹ, Jul 08 '13 at 13:33
@Hosein **[Please refer this answer](http://stackoverflow.com/questions/15955962/auto-calculating-columns-in-a-sql-table-based-on-prevoius-row-data/15969926#15969926)** — Prahalad Gaggar, Jul 08 '13 at 13:50

score 6 · Accepted Answer · edited May 23 '17 at 12:02

6

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

edited May 23 '17 at 12:02

Community

1
1

answered Jul 08 '13 at 12:59

ypercubeᵀᴹ

113,259
19
174
235

I didn't know of that and thank you. Sorry it's not available in 2008. Thanks man. – Hosein Jul 08 '13 at 13:07
1

No problem. Follow the links to see why a cursor would be the most efficient solution in your version. – ypercubeᵀᴹ Jul 08 '13 at 13:44
Great solution my friend. It's Complete and works in a completely reasonable time. Thank you so much. – Hosein Jul 08 '13 at 23:04
Thanks for that, I used it with an additional `PARTITION BY` clause required by my use case, it's by far the most readable solution and it's performing nicely. – Guillaume86 Mar 23 '16 at 09:40
@Guillaume86 Yes, efficiency is usually very good with proper indexing, e.g. an `(id) INCLUDE (value)` in this case. Or a `(partition_by_column, id) INCLUDE (value)` if you have `partition by`. – ypercubeᵀᴹ Mar 23 '16 at 09:44

score 2 · Answer 2 · edited Dec 19 '16 at 16:36

2

You can use recursion:

;WITH x AS
(
    SELECT 
        [ID],
        [Value],
        bal=[Value]
    FROM Table1
    WHERE [ID] = 1
    UNION ALL
    SELECT 
        y.[ID],
        y.[Value],
        x.bal+(y.[Value]) as bal
    FROM x INNER JOIN Table1 AS y
    ON y.[ID] = x.[ID] + 1
)
SELECT 
    [ID],
    [Value],
    AccValue= bal
FROM x
order by ID
OPTION (MAXRECURSION 10000);

SQL FIDDLE

edited Dec 19 '16 at 16:36

ypercubeᵀᴹ

113,259
19
174
235

answered Jul 08 '13 at 13:56

Prahalad Gaggar

11,389
16
53
71

@ypercube: I think we can use Row_Number instead of ID to overcome that issue. – Hosein Jul 08 '13 at 22:28
1

@Luv: In comparison between your solution and ypercube's, I think his should be faster, because the cursor approach is dynamic and O(n), but the recursive solution is O(n^2). Although it's my guess and I should check if it's accurate or not. – Hosein Jul 08 '13 at 22:32

score 1 · Answer 3 · answered Jul 08 '13 at 12:55

1

The generic SQL way to do this is with a correlated subquery (at least, I think that is the cleanest way):

select t.*,
       (select sum(t2.value)
        from @TempTable t2
        where t2.ID <= t.ID
       ) AccValue
from @TempTable t

SQL Server 2012 has a cumulative sum function:

select t.*,
       sum(t.value) over (order by t.id) as AccValue
from @TempTable t

answered Jul 08 '13 at 12:55

Gordon Linoff

1,242,037
58
646
786

Thanks man. Unfortunately I'm using 2008 R2. Is the subquery solution any different from my inner join, considering the performance? – Hosein Jul 08 '13 at 13:14
@Hosein . . . The best way to check is by comparing the execution plans and the performance. Both should be reasonable with an index on `t(id, value)`, although I wouldn't be surprised if the subquery performed better. I prefer the subquery method because I find it more readable and there is not danger of losing any rows from a join. – Gordon Linoff Jul 08 '13 at 13:21

Accumulating in SQL

3 Answers3