Running Totals and Moving Averages: Simple Examples

11a - RUNNING TOTALS and MOVING AVERAGES  (VERY SIMPLE version)
---------------------------------------------------------------------
No setup needed. This file creates its own small table.

• Running total: add up all rows from the start until this row SUM(Amount) OVER (ORDER BY Day ROWS UNBOUNDED PRECEDING)

Moving average : average of this row and the few rows before it
                 AVG(Amount) OVER (ORDER BY Day ROWS 2 PRECEDING)   -> last 3 rows

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

IF DB_ID('SqlLearningDb') IS NULL
    CREATE DATABASE SqlLearningDb;
GO

USE SqlLearningDb;
GO

Small table: daily sales of 2 shops for 5 days

DROP TABLE IF EXISTS dbo.DailyShopSales;

CREATE TABLE dbo.DailyShopSales
(
    ShopName VARCHAR(10),
    SaleDay  INT,           -- day 1, 2, 3 ...
    Amount   INT
);

INSERT INTO dbo.DailyShopSales (ShopName, SaleDay, Amount) VALUES
('ShopA', 1, 100),
('ShopA', 2, 200),
('ShopA', 3, 150),
('ShopA', 4, 300),
('ShopA', 5, 250),
('ShopB', 1, 50),
('ShopB', 2, 80),
('ShopB', 3, 70),
('ShopB', 4, 120),
('ShopB', 5, 90);
GO

SELECT * FROM dbo.DailyShopSales;

(10 rows affected)

Running total for ShopA

Day 1 = 100, Day 2 = 100+200 = 300, Day 3 = 300+150 = 450 ...

SELECT SaleDay, Amount,
       SUM(Amount) OVER (ORDER BY SaleDay ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.DailyShopSales
WHERE ShopName = 'ShopA';

Running total for Each shop (starts again for ShopB)

SELECT ShopName, SaleDay, Amount,
       SUM(Amount) OVER (PARTITION BY ShopName ORDER BY SaleDay ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.DailyShopSales;

Running count: how many days so far

SELECT ShopName, SaleDay, Amount,
       COUNT(*) OVER (PARTITION BY ShopName ORDER BY SaleDay ROWS UNBOUNDED PRECEDING) AS DaysSoFar
FROM dbo.DailyShopSales;

Moving average of the last 3 days (this day + 2 days before)

"Amount * 1.0" keeps decimals (AVG of whole numbers drops them)

SELECT SaleDay, Amount,
       AVG(Amount * 1.0) OVER (ORDER BY SaleDay ROWS 2 PRECEDING) AS MovingAvg3Days
FROM dbo.DailyShopSales
WHERE ShopName = 'ShopA';

Moving SUM of the last 2 days (this day + 1 day before)

SELECT SaleDay, Amount,
       SUM(Amount) OVER (ORDER BY SaleDay ROWS 1 PRECEDING) AS Last2DaysTotal
FROM dbo.DailyShopSales
WHERE ShopName = 'ShopA';

Highest sale so far (running MAX)

SELECT SaleDay, Amount,
       MAX(Amount) OVER (ORDER BY SaleDay ROWS UNBOUNDED PRECEDING) AS BestDaySoFar
FROM dbo.DailyShopSales
WHERE ShopName = 'ShopA';

Practice Questions

1. Show the running total of ShopB only.
2. Show a moving average of the last 2 days for each shop.
3. Show the LOWEST sale so far for each shop (running MIN).