Running Totals in SQL Server

Example Use Case: Inventory Forecasting

A practical business example is predicting closing inventory levels.

By calculating a running total of daily predicted sales alongside expected incoming supplier shipments, inventory planners can immediately identify potential stock-outs or overstock scenarios before they occur:

• Financial Reporting: Monitoring year-to-date (YTD) revenue, running cash balances, and budget consumption.

• Sales & Performance Tracking: Evaluating cumulative target progression across months, quarters, or campaigns.

• Inventory & Supply Chain: Predicting stock depletion rates to identify potential stock-outs early. 

Returning to the inventory forecasting example, tracking running balances enables supply chain teams to highlight impending stock shortages early. This provides actionable visibility to schedule replenishment deliveries at precisely the right time, preventing out-of-stock scenarios without holding excess capital in safety stock. For large-scale operational environments with millions of transactions, integrating these calculations directly into your Data Warehouse ensures reporting stays fast and responsive.

Let’s walk through a practical example step-by-step.

Suppose you have a table named [dbo].[SalesSample] containing daily sales records:

To see the cumulative total of sales at the end of each day, SQL window functions make the query straightforward and elegant.

By combining the SUM() aggregate function with an OVER (ORDER BY ...) clause, SQL Server calculates a cumulative total for each row sequentially:

1. SUM(Sales_Gross)

Specifies the aggregation method and target measure. While running totals most commonly use SUM(), you can also swap this for AVG(), COUNT(), MIN(), or MAX() to calculate running averages or moving extrema.

2. OVER (ORDER BY SaleDate ASC)

Defines the window frame and sequencing logic. The ORDER BY clause instructs SQL Server to order rows chronologically before applying the cumulative sum, ensuring each row includes all preceding rows up to that point.

3. AS [Running Total]

Assigns a clear, descriptive column alias to the resulting output field.

By swapping out SUM(), you can easily adapt this logic to calculate other rolling calculations:

• Running Count: COUNT(OrderID) OVER (ORDER BY SaleDate ASC) to track transaction growth over time.

• Running Average: AVG(Sales_Gross) OVER (ORDER BY SaleDate ASC) to smooth out daily fluctuations.

• Moving Extrema: MAX(Sales_Gross) or MIN(Sales_Gross) to track historical peaks and troughs.

Running totals are an essential technique for data analysis, financial accounting, and inventory forecasting. They allow you to track cumulative trends over time and see historical balances at any given point.

Calculating running totals in SQL Server is straightforward using the SUM() aggregate function combined with the OVER (ORDER BY ...) window clause. To calculate separate running totals across categories, such as by department, region, or product line, simply add a PARTITION BY clause.