|
Custom Search
| |
|
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. 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. 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.
Happy Coding!
Other tutorials in this category 1. MySQL Northwind Queries - Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |