Calculate a Running Total in SQL Server

Using join
Another variation is to use join. Now the query could look like:

SELECT a.id, a.value, SUM(b.Value)FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value
ORDER BY a.id;

for more you can visite this link

[To see links please register here]

BEGIN TRAN
CREATE TABLE #Table (_Id INT IDENTITY(1,1) ,id INT , somedate VARCHAR(100) , somevalue INT)


INSERT INTO #Table ( id , somedate , somevalue )
SELECT 45 , '01/Jan/09', 3 UNION ALL
SELECT 23 , '08/Jan/09', 5 UNION ALL
SELECT 12 , '02/Feb/09', 0 UNION ALL
SELECT 77 , '14/Feb/09', 7 UNION ALL
SELECT 39 , '20/Feb/09', 34 UNION ALL
SELECT 33 , '02/Mar/09', 6

;WITH CTE ( _Id, id , _somedate , _somevalue ,_totvalue ) AS
(

SELECT _Id , id , somedate , somevalue ,somevalue
FROM #Table WHERE _id = 1
UNION ALL
SELECT #Table._Id , #Table.id , somedate , somevalue , somevalue + _totvalue
FROM #Table,CTE
WHERE #Table._id > 1 AND CTE._Id = ( #Table._id-1 )
)

SELECT * FROM CTE

ROLLBACK TRAN

If you are using Sql server 2008 R2 above. Then, It would be shortest way to do;

Select id
,somedate
,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable

[LAG](

[To see links please register here]

) is use to get previous row value. You can do google for more info.




[1]:

Though best way is to get it done will be using a window function, it can also be done using a simple **correlated sub-query**.

Select id, someday, somevalue, (select sum(somevalue)
from testtable as t2
where t2.id = t1.id
and t2.someday <= t1.someday) as runningtotal
from testtable as t1
order by id,someday;

Here are 2 simple ways to calculate running total:

**Approach 1**: It can be written this way if your DBMS supports Analytical Functions

SELECT id
,somedate
,somevalue
,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM TestTable

**Approach 2**: You can make use of OUTER APPLY if your database version / DBMS itself does not support Analytical Functions

SELECT T.id
,T.somedate
,T.somevalue
,runningtotal = OA.runningtotal
FROM TestTable T
OUTER APPLY (
SELECT runningtotal = SUM(TI.somevalue)
FROM TestTable TI
WHERE TI.somedate <= S.somedate
) OA;

Note:- If you have to calculate the running total for different partitions separately, it can be done as posted here:

[To see links please register here]

While Sam Saffron did great work on it, he still didn't provide **recursive common table expression** code for this problem. And for us who working with SQL Server 2008 R2 and not Denali, it's still fastest way to get running total, it's about 10 times faster than cursor on my work computer for 100000 rows, and it's also inline query.<br>
So, here it is (I'm supposing that there's an `ord` column in the table and it's sequential number without gaps, for fast processing there also should be unique constraint on this number):

;with
CTE_RunningTotal
as
(
select T.ord, T.total, T.total as running_total
from #t as T
where T.ord = 0
union all
select T.ord, T.total, T.total + C.running_total as running_total
from CTE_RunningTotal as C
inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)

-- CPU 140, Reads 110014, Duration 132

**<kbd>[sql fiddle demo](

[To see links please register here]

;**

**update**
I also was curious about this **update with variable** or **quirky update**. So usually it works ok, but how we can be sure that it works every time? well, here's a little trick (found it here -

[To see links please register here]

) - you just check current and previous `ord` and use `1/0` assignment in case they are different from what you expecting:

declare @total int, @ord int

select @total = 0, @ord = -1

update #t set
@total = @total + total,
@ord = case when ord <> @ord + 1 then 1/0 else ord end,
------------------------
running_total = @total

select * from #t

-- CPU 0, Reads 58, Duration 139

From what I've seen if you have proper clustered index/primary key on your table (in our case it would be index by `ord_id`) update will proceed in a linear way all the time (never encountered divide by zero). That said, it's up to you to decide if you want to use it in production code :)

**update 2** I'm linking this answer, cause it includes some useful info about unreliability of the quirky update - [nvarchar concatenation / index / nvarchar(max) inexplicable behavior](

[To see links please register here]

).