Advanced query techniques
Running totals and moving averages
The frame determines the metric’s meaning.
Understand the concept
A running total accumulates values through the current row. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly selects rows in sequence. With ties, RANGE can include peers sharing the same ordering value.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row moving window, not necessarily three days. Missing dates require a calendar and daily-grain preparation for a true last-three-days metric. Establish the reporting grain before applying a window.
At the beginning, the window is not full; the average uses available rows. Decide whether missing days mean zero sales according to the business definition, not merely the SQL implementation.
Use it for accumulated sales, recent-record averages and period comparisons.
Calling a three-row window three days when dates are missing.
One topic. Three levels of depth.
SELECT ProductId, Stock, SUM(Stock) OVER (ORDER BY ProductId ROWS UNBOUNDED PRECEDING) AS RunningStock FROM lab.Product ORDER BY ProductId;Accumulates stock in product order.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Calculate salary accumulation in employee-key order.
Show the solution and explanation
SELECT EmployeeId, Salary, SUM(Salary) OVER (ORDER BY EmployeeId ROWS UNBOUNDED PRECEDING) AS RunningSalary FROM lab.Employee ORDER BY EmployeeId;This order is for demonstration; choose a meaningful business order for a real analysis.