Reputation: 581
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?
Upvotes: 5
Views: 8060
Reputation: 11609
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);
Upvotes: 2
Reputation: 115650
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
Upvotes: 6
Reputation: 1271131
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
Upvotes: 1