DatawarehouseETLSQL QueriesSQL Tips

How to create a Running Total Function in SQL

By 28/11/2022 October 8th, 2023 No Comments

Problem Statement:

In SQL, a running total is the cumulative sum of the previous numbers in a column.

Code Snippet:

-- Create a view that sums sales per city per year
USE InternetSales
GO
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

Leave a Reply