MySQL Query to Calculate Cumulative Sales Per Quarter

Calculate the Cumulative Sum of Sales by Quarter

The cumulative sum of sales is calculated by adding the current row's sales value to the sum of all previous rows' sales values, based on a specified order (e.g., by date or month). In MySQL, this is achieved using the SUM() function with the OVER() clause, specifying the ordering (e.g., ORDER BY date). For example, if monthly sales are 10, 20, and 30, the cumulative sums would be 10, 30, and 60, showing the running total over time.

Write a MySQL query to calculate the cumulative sum of sales for each quarter using a window function.

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the QuarterlySales table
SELECT 
    -- Retrieve the Quarter column to indicate the time period for each sales record
    Quarter, 
    -- Retrieve the Sales column to show the sales amount for each quarter
    Sales,
    -- Calculate cumulative sales using the SUM function as a window function:
    -- SUM(Sales) OVER (ORDER BY Quarter) computes the running total of sales.
    -- ORDER BY Quarter ensures that the sales are summed sequentially over time.
    SUM(Sales) OVER (ORDER BY Quarter) AS CumulativeSales
-- Specify the QuarterlySales table as the source of the data
FROM QuarterlySales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the running total of sales for each quarter.
• This demonstrates the use of the SUM() window function to compute a cumulative sum.
• Key Components:
• SUM(Sales) OVER (ORDER BY Quarter): Calculates the cumulative sum of sales ordered by quarter.
• SELECT Quarter, Sales: Retrieves the quarter and sales data along with the cumulative sum.
• Why use Window Functions?:
• Window functions allow you to perform cumulative calculations without the need for complex subqueries or joins.
• Real-World Application:
• For example, in a retail business, you might want to track the cumulative sales over the year to analyze growth trends.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the cumulative percentage of total annual sales per quarter.
• Write a MySQL query to find the sales difference between consecutive quarters.
• Write a MySQL query to compute a moving average of sales over a rolling 4-quarter window.
• Write a MySQL query to find the quarter with the highest sales growth compared to the previous quarter.

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

Previous MySQL Exercise: Calculate the Difference between Current and Next Salary.
Next MySQL Exercise: Find the Top 5 Highest Sales by Region.