SQL Server Interview Question #98

How do you calculate a running total?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

A running total can be calculated with SUM as a window function and an ordered ROWS frame. Each row then contains the cumulative sum from the start of the partition through the current row.

An explicit ROWS frame is often preferable because it makes the intended behavior clear, particularly when multiple rows share the same ORDER BY value.

Code Example

SELECT Id,
       OrderDate,
       TotalAmount,
       SUM(TotalAmount) OVER
       (
           ORDER BY OrderDate, Id
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
       ) AS RunningTotal
FROM dbo.Orders;