COMPUTING ROLLING & CUMULATIVE STATS – 3

This is a continuing series for “Computing Running and Cumulative Average“.

A cumulative sum (or running total) refers to a data set’s partial sum. It is a sum of a number sequence that is refreshed whenever a new number is added to the sequence.

The cumulative sum (or running total) is quite common in finance and trend analysis. Especially when dealing with daily data such as daily sales, daily bank balances, and so on. Depending on the measurements, it displays the total number for each day or month. A running total is the total of the previous numbers in a column. Window operations act on a collection of rows, returning an aggregate value for each row in the result set.

Moving total, on the other hand, is a time series technique for evaluating and finding patterns in data (also known as rolling means/rolling averages/rolling totals). For each moment in time, they are calculated as the mean of the current and a defined number of immediately preceding values. The fundamental concept is to look at how these totals (or averages) perform over time rather than how the original or raw data pieces behave.

Example:

Example dataset
CREATE TABLE DXB_Products(
Products VARCHAR(20),
Quantity INT,
Price DECIMAL(9,2),
SoldDate DATETIME);

CREATE TABLE SHJ_Products(
Products VARCHAR(20),
Quantity INT,
Price DECIMAL(9,2),
SoldDate DATETIME);

INSERT INTO DXB_Products VALUES
(‘Computers’,10,1200,’2022-09-20 13:15:00′),
(‘Televisions’,12,1000,’2022-09-20 16:59:00′),
(‘Xerox Machines’,3,1500,’2022-08-18 19:41:00′),
(‘Mobile Phones’,200,1000,’2022-09-21 11:25:25′),
(‘Laptops’,20,2000,’2022-08-11 08:49:00′),
(‘Tabs’,32,500,’2022-09-22 01:00:00′),
(‘iPads’,40,400,’2022-08-17 09:22:00′),
(‘iPods’,50,599,’2022-09-21 22:00:00′);

INSERT INTO SHJ_Products VALUES
(‘Computers’,10,1200,’2022-09-20 14:15:00′),
(‘Printers’,31,200,’2022-09-21 16:59:00′),
(‘Xerox Machines’,3,1500,’2022-08-18 21:41:00′),
(‘Scanners’,20,100,’2022-09-21 16:25:25′),
(‘Laptops’,20,2000,’2022-08-11 08:44:00′),
(‘Tabs’,32,500,’2022-09-22 02:00:00′),
(‘Headphones’,20,50,’2022-08-17 09:54:00′),
(‘iPods’,50,599,’2022-09-22 22:00:00′);

There are two sets of sales records for two different cities/branches. These sales records must be integrated, and the sales rolling totals must be identified.

SELECT
—Products,
—Quantity,
—Price,
—SoldDate,
—SUM(Price*Quantity) OVER(ORDER BY SoldDate) ‘CumulativeSum’ FROM(
———SELECT Products, Quantity, Price, SoldDate
———FROM DXB_Products
———WHERE Price > 500
—UNION ALL
———SELECT Products, Quantity, Price, SoldDate
———FROM SHJ_Products
———WHERE Price > 500
) A