MySQL Query to Calculate Cumulative Percentage of Sales

Calculate Cumulative Percentage of Total Sales

The cumulative percentage of total sales for each month is the running sum of monthly sales divided by the overall total sales, expressed as a percentage. It shows how much of the total sales has been accumulated up to each month, helping to track progress or trends over time. For example, if total sales are 100 and monthly sales are 10, 20, and 30, the cumulative percentages would be 10%, 30%, and 60%.

Write a MySQL query to calculate the cumulative percentage of total sales for each month using a window function.

Solution:

-- Begin the SELECT statement to define which columns and calculations to retrieve
SELECT 
    -- Retrieve the Month column to display the month associated with each sales record
    Month, 
    -- Retrieve the Sales column to show the sales amount for each month
    Sales,
    -- Calculate the cumulative percentage of total sales up to the current month:
    -- SUM(Sales) OVER (ORDER BY Month) computes the running total of sales, ordered by month.
    -- SUM(Sales) OVER () calculates the total sales over all months.
    -- Dividing the running total by the total sales and multiplying by 100 gives the cumulative percentage.
    SUM(Sales) OVER (ORDER BY Month) / SUM(Sales) OVER () * 100 AS CumulativePercentage
-- Specify the source table that contains the monthly sales data
FROM MonthlySales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the cumulative percentage of total sales for each month.
• This demonstrates the use of the SUM() window function to compute both a running total and the total sum.
• Key Components:
• SUM(Sales) OVER (ORDER BY Month): Calculates the running total of sales.
• SUM(Sales) OVER (): Calculates the total sum of sales.
• SUM(Sales) OVER (ORDER BY Month) / SUM(Sales) OVER () * 100: Computes the cumulative percentage.
• Why use Window Functions?:
• Window functions allow you to perform complex calculations, such as cumulative percentages, without the need for multiple subqueries.
• Real-World Application:
• For example, in a retail business, you might want to track the contribution of each month's sales to the total annual sales.

For more Practice: Solve these Related Problems:

• Write a MySQL query to compute the cumulative percentage of sales within each product category.
• Write a MySQL query to calculate the cumulative percentage of sales while excluding the top 5% of sales.
• Write a MySQL query to calculate cumulative sales as a percentage of monthly sales instead of total sales.
• Write a MySQL query to compute cumulative sales while resetting for each quarter.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Find the difference between Current and Previous Salary.
Next MySQL Exercise: Partition Employees by Department and Rank by Salary.