The T-SQL language constantly undergoes modifications. Every release bring in something new, something to help develop an easier solution to a common business problem. With Microsoft SQL Server 2012, enhancements have been incorporated to the Windows functions to allow an easier solution to what is commonly referred to as the “Running Total Problem”.

The whole concept of running totals is that as the transactions continue to be added, the system maintains the total of a given sequence. This allows the system to fetch this summary without having to maintain any record of the individual transactions itself.

Here’s an example based on the transactions for one of the SalesOrders in the AdventureWorks2012 sample database.

Essentially, the running total at each row is the sum of the running total value from the previous row and the expression to be added from the current row (in this case, Unit Price * Order Quantity). That’s where the problems start in SQL Server.

The running total is calculated in a sequence and unfortunately, SQL Server does not guarantee the order of records in a result set which makes it difficult to compute the running total in a SET based operation. The solution to this problem has therefore been a CURSOR operation where each record is independently processed in a loop.

However, the T-SQL enhancements made in SQL Server 2012 have a possible solution to this problem. The the aggregate function SUM() has been enhanced to operate as a windowing function which now supports the ORDER BY clause in the OVER clause. This gives the ability for SUM to be performed in the intended sequence of the transactions. Here’s a query to demonstrate this:

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SalesOrderDetailID,
       UnitPrice,
       OrderQty,
       LineTotal,
       SUM(UnitPrice * OrderQty) 
            OVER (PARTITION BY SalesOrderId 
                  ORDER BY SalesOrderDetailID
                 ) 
                 AS RunningTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659;
GO

NOTE: Attempting to run this query against a SQL Server 2008R2 or lower environment will result in an error related to missing support of the ORDER BY clause. They support the use of OVER and PARTITION BY with the SUM() function, but do not support ORDER BY.

The result set available from the query is shown below.

As can be seen from the result set, Microsoft SQL Server auto-calculated the running total for the given Sales Order in a given sequence. These results also match with the table provided above.

Finally, the purpose of a running total is that a system/user should not have to maintain a record of all the transactions. Hence, the maximum value of the running total should match the Sub-total stored on the SalesOrder header record.

USE AdventureWorks2012;
GO
SELECT SalesOrderID,
       SubTotal,
       TaxAmt,
       Freight,
       TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43659;
GO

Summary

The ORDER BY clause can be used when using SUM() over a window defined by the OVER clause to generate a running total of values in a given sequence.

It is really amazing that the solution to  a problem that has challenged many brains over the years can be this simple. Kudos to the #SQLServer product development team at #Microsoft. Thanks, guys!

Further Reading

• Windowing functions – Underappreciated features of Microsoft SQL Server [Link]
  
• Aggregate Function – SUM [Link]
  
• Over Clause [Link]
  
• Running Totals, Wikipedia [Link]

Until we meet next time,

Be courteous. Drive responsibly.