SQL Tips by Namwar Rizvi

May 5, 2007

Caluclating running total

Filed under: Common Table Extressions,CTE,Running Total — namwar @ 9:27 PM

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

Advertisements

1 Comment »

  1. I was kind of searching this forever but got the perfect example but to my surprise CTE gives slower result that my cross join query….

    Comment by raveendra — October 6, 2011 @ 4:50 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: