Course contentsShow
← Data Science
Lesson 1016 of 2,14526. Advanced SQLFree lesson

Cumulative Sums and Running Totals

Calculate running totals using SUM() OVER with appropriate frame specifications.

Cumulative Sums and Running Totals

You'll learn how to calculate running totals using window functions with precise frame specifications.

A running total (or cumulative sum) is a sum that grows as you move through your data, accumulating values row by row. Think of it like tracking your bank balance: each transaction adds to (or subtracts from) the running total of all previous transactions, including the current one.

The Core Pattern

To create a running total, you use SUM() with the OVER clause and specify a frame that includes all rows from the start of the partition up to the current row:

SELECT 
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions;

Understanding the Frame Specification

  • UNBOUNDED PRECEDING: Start from the very first row in the ordered set
  • CURRENT ROW: Include the current row in the calculation
  • ORDER BY: Critical! Defines the sequence for accumulation

This frame specification tells SQL: "For each row, sum all values from the beginning of the result set up through the current row."

Shorthand Syntax

Because running totals are so common, you can use a simpler default syntax:

SUM(amount) OVER (ORDER BY transaction_date)

When you specify ORDER BY without an explicit frame, SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which typically behaves like the ROWS version for running totals.

Key Takeaway: Running totals use SUM() OVER with ORDER BY and a frame from UNBOUNDED PRECEDING to CURRENT ROW, accumulating values progressively through your ordered dataset.