Running total

The textbook case of row-by-row processing: a running total is computed in a single pass, provided the window is properly defined.

≈ 3 min readReading

On this page

Problem statement
Data set
Procedural versus set-based
1. Procedural
2. Set-based
Expected result
Why set-based is preferable
Pitfalls
Variants
Verifying equivalence
SQL Server versions

Problem statement#

For each sale, display the running total of the amounts in chronological order (then by identifier when dates are equal).

Data set#

CREATE TABLE dbo.Ventes
(
    VenteId   int           NOT NULL PRIMARY KEY,
    DateVente date          NOT NULL,
    Montant   decimal(18,2) NOT NULL
);

INSERT INTO dbo.Ventes (VenteId, DateVente, Montant)
VALUES (1, '2026-01-01', 100.00),
       (2, '2026-01-02',  50.00),
       (3, '2026-01-02',  25.50),   -- same date as sale 2
       (4, '2026-01-05',  10.00);

Procedural versus set-based#

Procedural#

DECLARE @Resultat TABLE (VenteId int PRIMARY KEY, DateVente date, Montant decimal(18,2), Cumul decimal(18,2));
DECLARE @Id int, @Date date, @Montant decimal(18,2), @Cumul decimal(18,2) = 0;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT VenteId, DateVente, Montant FROM dbo.Ventes ORDER BY DateVente, VenteId;
OPEN c;
FETCH NEXT FROM c INTO @Id, @Date, @Montant;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cumul += @Montant;
    INSERT INTO @Resultat (VenteId, DateVente, Montant, Cumul)
    VALUES (@Id, @Date, @Montant, @Cumul);
    FETCH NEXT FROM c INTO @Id, @Date, @Montant;
END;
CLOSE c;
DEALLOCATE c;

SELECT * FROM @Resultat ORDER BY DateVente, VenteId;

Set-based#

SELECT VenteId, DateVente, Montant,
       SUM(Montant) OVER (ORDER BY DateVente, VenteId
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul
FROM   dbo.Ventes
ORDER BY DateVente, VenteId;

Expected result#

Four rows, whose running total is successively 100.00 · 150.00 · 175.50 · 185.50.

Why set-based is preferable#

The running total is computed in a single pass over the sorted data: the engine maintains the current sum without rereading the table.
The procedural version performs, for each row, one cursor read, one assignment and one insert.
An index on (DateVente, VenteId) with Montant as an included column avoids a sort: CREATE INDEX IX_Ventes_Date ON dbo.Ventes (DateVente, VenteId) INCLUDE (Montant);

Pitfalls#

Default frame: with ORDER BY alone, the frame is RANGE UNBOUNDED PRECEDING: sales 2 and 3 (same date) receive the same running total (175.50). With ROWS but a non-unique ordering, the order among ties is arbitrary. For a deterministic row-by-row running total: ROWS and a unique ordering.
The triangular join looks set-based, but reads about n²/2 rows: it collapses on large volumes.

-- Compare three formulations on sales 2 and 3 (same date)
SELECT VenteId, DateVente, Montant,
       SUM(Montant) OVER (ORDER BY DateVente)                                   AS Cumul_cadre_par_defaut,
       SUM(Montant) OVER (ORDER BY DateVente ROWS UNBOUNDED PRECEDING)          AS Cumul_ROWS_ordre_non_unique,
       SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) AS Cumul_ROWS_ordre_unique
FROM   dbo.Ventes
ORDER BY DateVente, VenteId;

-- Set-based but badly done: triangular join (cost of n² / 2 reads)
SELECT a.VenteId, SUM(b.Montant) AS Cumul
FROM   dbo.Ventes AS a
JOIN   dbo.Ventes AS b
       ON  b.DateVente < a.DateVente
       OR (b.DateVente = a.DateVente AND b.VenteId <= a.VenteId)
GROUP BY a.VenteId;

Variants#

-- If the table had a ClientId: running total per customer and per year
SUM(Montant) OVER (PARTITION BY ClientId, YEAR(DateVente)
                   ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING)

-- Moving average over the last 7 rows
AVG(Montant) OVER (ORDER BY DateVente, VenteId ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

-- Run in the same batch as the procedural version (the @Resultat variable must exist)
SELECT VenteId, Cumul FROM @Resultat
EXCEPT
SELECT VenteId, SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) FROM dbo.Ventes;

SELECT VenteId, SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS UNBOUNDED PRECEDING) FROM dbo.Ventes
EXCEPT
SELECT VenteId, Cumul FROM @Resultat;
-- Expected result: no rows in either query.

SQL Server versions#

Window functions with a ROWS frame: SQL Server 2012 and later (Azure SQL Database included).