Get the Running Total of Sales for Each Month Using SQL

Get the Running Total of Sales for Each Month Using MS SQL Server Query?

To calculate the running total of sales for each month in SQL Server, you can use the SUM() window function. This function allows you to compute cumulative totals within a specified window (or range) of data.

Assuming you have a table named Sales with the following columns:

• SaleDate: The date of the sale.
• Amount: The amount of the sale.

Here’s how you can calculate the running total of sales for each month:

SQL Query

WITH MonthlySales AS (

    SELECT

        DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS MonthStart,

        SUM(Amount) AS MonthlyTotal

    FROM Sales

    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)

),

RunningTotal AS (

    SELECT

        MonthStart,

        MonthlyTotal,

        SUM(MonthlyTotal) OVER (ORDER BY MonthStart) AS RunningTotal

    FROM MonthlySales

)

SELECT

    MonthStart,

    MonthlyTotal,

    RunningTotal

FROM RunningTotal

ORDER BY MonthStart;

Explanation:

1. CTE MonthlySales:

   • DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS MonthStart: This expression normalizes the SaleDate to the first day of the month, which allows you to group sales by month.
   • SUM(Amount) AS MonthlyTotal: Aggregates the sales amounts to get the total sales for each month.
   • GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0): Groups the data by the start of each month.

2. CTE RunningTotal:

   • SUM(MonthlyTotal) OVER (ORDER BY MonthStart) AS RunningTotal: Calculates the running total of monthly sales using the SUM() window function, which accumulates the total sales over time ordered by MonthStart.

3. Final SELECT Statement:

   • Retrieves the monthly totals and their corresponding running totals, and orders the results by the month.

Example Data and Results

Assume the Sales table has the following data:

SaleDate	Amount
2024-01-15	100
2024-01-22	150
2024-02-05	200
2024-02-20	250
2024-03-10	300

The MonthlySales CTE will compute:

MonthStart	MonthlyTotal
2024-01-01	250
2024-02-01	450
2024-03-01	300

The RunningTotal CTE will then compute:

MonthStart	MonthlyTotal	RunningTotal
2024-01-01	250	250
2024-02-01	450	700
2024-03-01	300	1000

This result shows the running total of sales for each month.

You can execute this query in SQL Server Management Studio or any other SQL query tool connected to your SQL Server to get the running total of sales by month.