How to generate Cumulative Sum (running total) by using MySQL RANK() function

How to generate Cumulative Sum (running total) by MySQL - Part 2

In Part 1 of this tutorial, we have shown how to calculate running totals (aka cumulative sums) by using self join. In the join clause, we use a naturally available rank value where the left table (t1) is always coded greater than or equal to the RANK value in the right table (t2), e.g. t1.RankValue >= t2.RankValue.

In Part 2 of this tutorial, we'll show how to deliberately create a RANK value by using MySQL's ranking functions such as RANK(), DENSE_RANK(), or ROW_NUMBER(). Learning this technique is important because a natural rank value is not always available. In such a case, the only way is to calculate and then assign rank values to each group within which the running total is worked out.

Practice 1: Use RANK() function to create a rank value

The queries below are a rewrite of Practice 2 in Part 1 of this tutorial. The only difference is that RANK() function is added to create a rank value even though OrderYear can naturally act as a rank value.

1.1 Build the basis set

-- This is the basic set. Here a rank value is created within each product category 
-- by using RANK() function.
select c.CategoryName,
	year(b.OrderDate) as OrderYear,
	round(sum(a.UnitPrice*a.Quantity*(1-a.Discount)),2) as YearlySales,
	RANK() OVER (PARTITION BY c.CategoryName ORDER BY b.OrderDate) as RankValue
from Order_Details as a
inner join Orders b on a.OrderID = b.OrderID
inner join Products as p on a.ProductID=p.ProductID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryName,year(b.OrderDate)
order by c.CategoryName, OrderYear;

24 records returned.
Yearly sales by RANK function

1.2 Use RANK() function to calculate running total

-- Calculate running total. Rank value is used in the join clause.
select t1.CategoryName,
	t1.OrderYear,
	t1.YearlySales,
	sum(t2.YearlySales) as RunningTotal
from
(
	select c.CategoryName,
		year(b.OrderDate) as OrderYear,
		round(sum(a.UnitPrice*a.Quantity*(1-a.Discount)),2) as YearlySales,
        RANK() OVER (PARTITION BY c.CategoryName ORDER BY b.OrderDate) as RankValue
	from Order_Details as a
	inner join Orders b on a.OrderID = b.OrderID
	inner join Products as p on a.ProductID=p.ProductID
	inner join Categories as c on c.CategoryID=p.CategoryID
	group by c.CategoryName,year(b.OrderDate)	
) as t1 -- derived table
inner join 
(
	select c.CategoryName,
		year(b.OrderDate) as OrderYear,
		round(sum(a.UnitPrice*a.Quantity*(1-a.Discount)),2) as YearlySales,
        RANK() OVER (PARTITION BY c.CategoryName ORDER BY b.OrderDate) as RankValue
	from Order_Details as a
	inner join Orders b on a.OrderID = b.OrderID
	inner join Products as p on a.ProductID=p.ProductID
	inner join Categories as c on c.CategoryID=p.CategoryID
	group by c.CategoryName,year(b.OrderDate)
) as t2
on t1.CategoryName=t2.CategoryName and t1.RankValue >= t2.RankValue
group by t1.CategoryName,t1.OrderYear
order by t1.CategoryName,t1.OrderYear;

24 records returned.
Yearly sales running total

Practice 2: Use RANK() function when a natural rank value is not available

In the query below, we want to calculate sales running total based on employee names within each product. Because a natural rank value is not available, we have to use RANK() function to produce the rank value within each product (this is the GROUP) based on employee names in alphabetical order.

2.1 Load the basic set into a physical MySQL table

-- Create the base table 
create table ProductSalesByEmployee
as
select p.ProductName,
	e.FirstName as EmployeeName,
	round(sum(a.UnitPrice*a.Quantity*(1-a.Discount)),2) as Sales,
	RANK() OVER (PARTITION BY p.ProductName ORDER BY e.FirstName) as RankValue
from Order_Details as a
inner join Orders b on a.OrderID = b.OrderID
inner join products p on a.ProductID=p.ProductID
inner join Employees e on e.EmployeeID=b.EmployeeID
group by p.ProductName,e.FirstName
order by p.ProductName,e.FirstName;

2.2 Use RANK() function to calculate running total because a natural ranking column is not available

-- Calculate running total
select t1.ProductName,
	t1.EmployeeName,
	t1.Sales,sum(t2.Sales) as RunningTotal
from ProductSalesByEmployee as t1
inner join ProductSalesByEmployee as t2 on t1.ProductName=t2.ProductName and t1.RankValue >= t2.RankValue
group by t1.ProductName,t1.EmployeeName
order by t1.ProductName,sum(t2.Sales);

589 records returned.
Running total by employee within each product

Happy Coding!

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Fill Gaps in Sales Data

6. How to Calculate Totals, Subtotals and Grand Total

7. How to Work with NULL Values

8. How to fill down empty cells with values from a previous non-empty row

9. Use RANK function to update a previous record within a group or partition

10. Two ways to add a unique number or ID to each row

11. 3 ways to get Top N rows from MySQL

12. How to generate Cumulative Sum (running total) by MySQL - Part 1

Back to Tutorial Index Page

	Custom Search