Calculating running total is a typical requirement of most several financial reports. These reports requires a field which displays the running total another field like Salary or Amount etc..
Following is the query to calculate running total for AdventureWorks Purchasing.PurchaseOrderHeader table:
–Actual Query
With
tblItemsWithRow(RowNum,PurchaseOrderID,TotalDue)
as
( Select Row_Number() Over(Order by PurchaseOrderID) as RowNum, PurchaseOrderID,TotalDue
from Purchasing.PurchaseOrderHeader
)
–Final Query to return the running total
Select RowNum,PurchaseOrderID,TotalDue,
( Select Sum(TotalDue) from tblItemsWithRow m where m.RowNum<= s.RowNum) as RunningTotal
from tblItemsWithRow s
