Windows Function - How to Calculate Running Total

Windows Function - How to Calculate Running Total

 

Query used:

 

-- Create a view that sums sales per city per year

 

 

 

 

 

CREATE VIEW CitySalesByYear

 

 

 

 

 

 

AS

 

 

SELECT c.City, YEAR(h.OrderDate) OrderYear, SUM(d.OrderQuantity) OrderQuantity

FROM SalesOrderHeader h

JOIN SalesOrderDetail d ON d.SalesOrderNumber = h.SalesOrderNumber

JOIN Customers c ON h.CustomerKey = c.CustomerKey

GROUP BY c.City, YEAR(h.OrderDate)

 

 

 

GO

 

 

 

 

 

-- View the data in the view

 

 

SELECT * FROM CitySalesByYear

ORDER BY City, OrderYear

 

 

 

GO

 

 

 

 

 

-- Use OVER to show a running total by year

 

 

SELECT City, OrderYear, OrderQuantity,

SUM(OrderQuantity) OVER (PARTITION BY City ORDER BY OrderYear

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningQty

FROM CitySalesByYear