Using a Computed Column to Calculate a Running Total

One common question I get is "How can a calculate a running total for a column in a table?"

Typically, this was done with a query using a self-join on the table. While this is a perfectly valid way to accomplish the goal, SQL Server 2005 added a new feature that makes it simpler to implement, executes much faster, and you only have to do it once!

To demonstrate these two techniques, we first need a table and some data:


Using a Self-Join Query

The typical way this was done in the past was to write a query that joined the table back to itself and used a SUM aggregate with a GROUP BY clause to calculate the running total at query time.

This will give the following resultset:







Using a Computed Column

SQL Server 2005 introduced a new feature called a computed column. This can be used to define a column as an expression instead of specifying a data type. Normally, this expression will only reference data in the same row to compute its value. Part of this feature that is often overlooked, is that you can use a User Defined Scalar Function call as the expression to calculate the columns value. We will take advantage of that to calculate the running totals.

The first thing we need to implement this is a function that will take a datetime parameter and calculate the running total for all rows with a datetime less than or equal to the value passed to the function.

Note: It should be obvious that to use this method, the column chosen must be unique and specify the order of the rows for the calculation.

The Function














To use this function, we need to alter the definition of the table we created earlier to add the computed column. The table script will now look like this:

Note the definition of the RunningTotal column.

Since the running totals are now calculated for us by the table definition itself, to get the same result set we can use this much simpler query:

The new Computed Column feature in SQL Server 2005 can be used in many other ways. To explore this useful tool more fully, please refer to:

http://msdn.microsoft.com/en-us/library/ms191250.aspx