DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to generate a RUNNING TOTAL column?

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

-- SQL Server running total - QUICK SYNTAX
SELECT SalesOrderID,
       OrderDate = CONVERT(date, OrderDate),
       O.TotalDue,
       (SELECT sum(TotalDue)
        FROM   AdventureWorks2008.Sales.SalesOrderHeader
        WHERE  SalesOrderID <= O.SalesOrderID) 'Running Total'
FROM   AdventureWorks2008.Sales.SalesOrderHeader O
GO
/* Partial results
 
SalesOrderID      OrderDate   TotalDue    Running Total
43659             2001-07-01  27231.5495  27231.5495
43660             2001-07-01  1716.1794   28947.7289
43661             2001-07-01  43561.4424  72509.1713
43662             2001-07-01  38331.9613  110841.1326
43663             2001-07-01  556.2026    111397.3352

*/
-- SQL running totals - calculating running totals in sql - t sql running total
-- SQL row number over order by
USE AdventureWorks;
GO
DECLARE  @Year  INT,  @Month INT
SET @Year = 2001
SET @Month = 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
/*  Partial results
 
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
*/
------------
 
-- SQL running total - SQL Server running total column - SQL correlated subquery
USE Northwind;
GO
SELECT   o1.OrderID,
         o1.ShipName,
         o1.ShipCity,
         OrderDate = convert(CHAR(10),OrderDate,112),
         o1.Freight,
         [FreightRunningTotal] =
           (SELECT '$' + convert(VARCHAR,sum(o2.Freight),1)
            FROM   Orders o2
            WHERE  o2.OrderID <= o1.OrderID
               AND o2.ShipCountry = 'USA')
FROM     Orders o1
WHERE    o1.ShipCountry = 'USA'
ORDER BY o1.OrderID
GO
 
/* Partial results:
 
OrderID     ShipName          ShipCity    OrderDate   Freight     FreightRunningTotal
10432 Split Rail Beer & Ale   Lander      19970131    4.34        $1,989.40
10440 Save-a-lot Markets      Boise       19970210    86.53       $2,075.93
10441 Old World Delicatessen  Anchorage   19970210    73.02       $2,148.95
10452 Save-a-lot Markets      Boise       19970220    140.26      $2,289.21
10469 White Clover Markets    Seattle     19970310    60.18       $2,349.39

*/

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

Related article:

How to architect running total using cursor? Contributed by Paul Nielsen, www.sqlserverbible.com

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE