MySQL Query to Compute Running Total of Sales

Calculate Running Total of Sales

A running total of sales is the cumulative sum of sales values over a sequence, where each subsequent value adds to the previous total. It provides a continuous aggregation of sales data, often used to track performance or growth over time. For example, if daily sales are 10, 20, and 30, the running total would be 10, 30, and 60.

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

Solution:

-- Start the SELECT statement to specify the columns to be retrieved
SELECT 
    -- Retrieve the Month column to show the month associated with the sales data
    Month, 
    -- Retrieve the Sales column to display the sales amount for each month
    Sales,
    -- Calculate the running total of Sales up to the current month using a window function
    SUM(Sales) OVER (ORDER BY Month) AS RunningTotal
-- Specify the table that contains the monthly sales data
FROM MonthlySales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the cumulative sum of sales over time, month by month.
• This demonstrates the use of the SUM() window function to compute a running total.
• Key Components:
• SUM(Sales) OVER (ORDER BY Month): Calculates the running total of sales ordered by month.
• SELECT Month, Sales: Retrieves the month and sales data along with the running total.
• 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 sales for each product category.
• Write a MySQL query to compute the cumulative total sales, resetting for each year.
• Write a MySQL query to calculate a running total but only include sales above a threshold of $500.
• Write a MySQL query to calculate a running total of sales while excluding the lowest 10% of transactions.

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

Previous MySQL Exercise: Rank Employees by Salary.
Next MySQL Exercise: Partition Employees by Department and Rank by Salary.