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

RollingTotals_SalesData

Hope you find this article helpful.

Happy learning!!

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s