This is not a new topic. If you search, you will surely find many posts on this, mostly with traditional techniques but not using newest capabilities such as SQL Windowing. Since I wrote a post on Analysis Services for the same, thought to write the same on T-SQL too. Here is the way of calculating running totals using Window components and functions which provides an efficient way of calculating and simple code structure.

The following query shows the way of calculating. The first code creates a window based on SalesOrderId (which is unique) and get the running totals over SubTotal for a given year. The second code creates a window on OrderDate (which is not unique). This will show the totals for the date instead of Running-Totals for the date unless the range is specified using boundaries. That is the reason for adding upper and lower boundaries using **ROW, UNBOUNED PRECEDING** and **CURRENT ROW** inside the window for restricting rows to be participated for the calculation.

- -- Window based on OrderId which is unique
- SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
- , SubTotal Total
- , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
- FROM Sales.SalesOrderHeader
- ORDER BY OrderDate, SalesOrderID
- -- Window based on OrderDate which is not unique
- SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
- , SubTotal Total
- , SUM(SubTotal) OVER(ORDER BY OrderDate
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal
- FROM Sales.SalesOrderHeader
- ORDER BY OrderDate, SalesOrderID

Both queries produce same result;

Here is a comparison on the same using traditional techniques. Though it shows that the query uses window is faster than other queries, always check and pick the best.

- -- Using Window components
- SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
- , SubTotal Total
- , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
- FROM Sales.SalesOrderHeader
- WHERE YEAR(OrderDate) = 2006
- ORDER BY OrderDate, SalesOrderID
- -- Using self-join
- SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
- , h1.SubTotal Total
- , SUM(h2.SubTotal) RunningTotal
- FROM Sales.SalesOrderHeader h1
- INNER JOIN Sales.SalesOrderHeader h2
- ON h1.SalesOrderID >= h2.SalesOrderID
- AND YEAR(h2.OrderDate) = 2006
- WHERE YEAR(h1.OrderDate) = 2006
- GROUP BY h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber
- , h1.CustomerID, h1.SubTotal
- ORDER BY h1.OrderDate, h1.SalesOrderID
- -- Using sub query
- SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
- , h1.SubTotal Total
- , (SELECT SUM(h2.SubTotal) RunningTotal
- FROM Sales.SalesOrderHeader h2
- WHERE h1.SalesOrderID >= h2.SalesOrderID
- AND YEAR(h2.OrderDate) = 2006)
- FROM Sales.SalesOrderHeader h1
- WHERE YEAR(h1.OrderDate) = 2006
- ORDER BY h1.OrderDate, h1.SalesOrderID