SQL Server - Simple CTE Example - Running Total

Hi all,

We all know how powerful and useful the SQL language can be. Optimise the code as well, and you get back what you asked for in a fraction of a second (well it’s a matter of speech, my stored procedures currently take a couple of hours to complete!!!)

Anyway, since last week, I have been trying to write some SQL code to perform something rather simple in programming terms, yet so complicated if you think in database terms. What I was trying to do was to calculate the running total of a column.

There are a lot of ways to deal with such requirements including self joins, cursors, etc. However, the aforementioned techniques are very slow and prone to error.

CTE (Common Table Expression) comes to the rescue!!! It was introduced in SQL Server 2005, and it can simplify the way we implement recursive queries to retrieve data presented in a hierarchical format. CTE can be thought of as a temporary result set which remains active for the duration of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )


To give a more specific example (and play a bit with CTE), let’s create the following table:

DECLARE @CAR_IMPROVEMENT TABLE
(
      PART_ID INT NOT NULL,
      PART_DESC VARCHAR(100) NOT NULL,
      PART_VALUE NUMERIC(10,2) NOT NULL,
      RUNNING_TOTAL NUMERIC(15,2) NULL
);

INSERT INTO @CAR_IMPROVEMENT (PART_ID, PART_DESC, PART_VALUE)
      SELECT 1, 'BBS Alloys', 1500
UNION ALL
      SELECT 2, 'Induction Kit', 230
UNION ALL
      SELECT 3, 'ECU Remap', 435
UNION ALL
      SELECT 4, 'Anti-roll Bars', 350
UNION ALL
      SELECT 5, 'CD Player', 168;

SELECT * FROM @CAR_IMPROVEMENT;

Running the above code will create a table variable and populate it with the values we provided. The end result can be seen below:

Now that we have created are table, we are ready to move to the interesting bit of calculating the running total of our performance parts. The following code does that with the use of the new WITH clause:

WITH CAR_IMPRO_RUNNING_TOTAL
(
      PART_ID,
      PART_DESC,
      PART_VALUE,
      RUNNING_TOTAL
)
AS
(
      SELECT
             A.PART_ID,
            A.PART_DESC,
            A.PART_VALUE,
            (SELECT
                  SUM(B.PART_VALUE)
            FROM @CAR_IMPROVEMENT AS B
            WHERE B.PART_ID <= A.PART_ID)
      FROM @CAR_IMPROVEMENT AS A
)

SELECT * FROM CAR_IMPRO_RUNNING_TOTAL;

The above code is able to calculate the running total line by line, by referencing the original table we created and applying the condition that the sum must be calculated up to an including the current record. The resultant table is the following:

We can see that in the first row the running total is just the first part’s value. In the second row, the first and second part’s values have been summed up to give a total of £1,730.00 (1,500.00 + 230.00). And the summation is continued until the end of the table.

On the next thread we will look at more complex examples and how to retrieve the running total in each case.

Till next time…