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
|
