How to generate a RUNNING TOTAL column?

Execute the following T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the generation of a Running Total columns.

Running total calculation applying CTE and ROW_NUMBER() function for row identification and sequencing.

------------

-- SQL Server Running Total Calculation

------------

DECLARE @DateStart date='2004-01-01'

;WITH CTE

     AS (SELECT ID = ROW_NUMBER() OVER(ORDER BY PurchaseOrderID),

                PurchaseOrderID, OrderDate = CONVERT(DATE,OrderDate),SubTotal

         FROM   AdventureWorks2008.Purchasing.PurchaseOrderHeader

         WHERE  OrderDate >= @DateStart)

SELECT   PurchaseOrderID,  OrderDate, SubTotal,

         RunningTotal = (SELECT SUM(SubTotal)

                         FROM   CTE

                         WHERE  ID <= A.ID)

FROM     CTE AS A

ORDER BY ID

/*

PurchaseOrderID   OrderDate   SubTotal    RunningTotal

1313              2004-01-03  91117.95    91117.95

1314              2004-01-03  525.00      91642.95

1315              2004-01-03  2003.6835   93646.6335

1316              2004-01-03  26455.275   120101.9085

1317              2004-01-03  944.37      121046.2785

.....

*/

------------

Running total on the TotalDue column of SalesOrderHeader table in AdventureWorks2008 sample database.

USE AdventureWorks2008; GO

DECLARE @Year INT = 2001, @Month INT = 7

SELECT   SequenceNo = ROW_NUMBER()

                        OVER(ORDER BY OrderDate, SalesOrderID),

         OrderDate = convert(CHAR(10),OrderDate,111),

         SalesOrderId, -- SQL dollar formatting - money / currency format 

         TotalDue = '$' + convert(VARCHAR,TotalDue,1),

         [Running Total] = '$' + convert(VARCHAR,

         (SELECT sum(TotalDue)

          FROM   Sales.SalesOrderHeader

          WHERE  SalesOrderID <= soh.SalesOrderID

               AND year(OrderDate) = @Year

               AND month(OrderDate) = @Month),

                                         1)

FROM     Sales.SalesOrderHeader soh

WHERE    year(OrderDate) = @Year

         AND month(OrderDate) = @Month

ORDER BY SequenceNo;

GO

/*

SequenceNo  OrderDate   SalesOrderId      TotalDue    Running Total

1           2001/07/01  43659             $27,231.55  $27,231.55

2           2001/07/01  43660             $1,716.18   $28,947.73

3           2001/07/01  43661             $43,561.44  $72,509.17

4           2001/07/01  43662             $38,331.96  $110,841.13

5           2001/07/01  43663             $556.20     $111,397.34

6           2001/07/01  43664             $32,390.20  $143,787.54

7           2001/07/01  43665             $19,005.21  $162,792.75

.....*/

---------

Running total on the Freight charges column of Orders table in Northwind sample database.


-- SQL running total - SQL Server running total column - SQL correlated subquery 
USE Northwind; 
GO 
 
SELECT   o1.OrderID, 
         o1.ShipName, 
         o1.ShipCity, 
         o1.Freight, 
         OrderDate = convert(CHAR(10),OrderDate,111), 
         [Running Total on Freight] = 
         (SELECT '$' + convert(VARCHAR,sum(o2.Freight),1) 
                     FROM   Orders o2 
                     WHERE  o2.OrderID <= o1.OrderID 
                     AND o2.ShipCountry = 'Canada') 
FROM     Orders o1 
WHERE    o1.ShipCountry = 'Canada' 
ORDER BY o1.OrderID 
GO 
/* Partial results
OrderID     ShipName          ShipCity    Freight     OrderDate   Running Total on Freight
10332       Mère Paillarde    Montréal    52.84       1996/10/17  $52.84
10339       Mère Paillarde    Montréal    15.66       1996/10/28  $68.50
10376       Mère Paillarde    Montréal    20.39       1996/12/09  $88.89
10389       Bottom-Dollar     Tsawassen   47.42       1996/12/20  $136.31
10410       Bottom-Dollar     Tsawassen   2.40        1997/01/10  $138.71

*/

Related links:

Calculating Running Totals in SQL Server 2005, The optimal solution?

http://www.sqlusa.com/bestpractices2005/runningtotalusingcursor/