Reporting Windows

Running Totals

Accumulate by Region

A reporting window can keep each detail row while adding a cumulative total for its group.

Program

Play the script to choose a region and watch monthly sales accumulate in report order.

wanted_region
regional_running_total.sql
Replay: real traced execution (multi-file project)
CREATE TABLE report_choice AS WITH params(wanted_region) AS (VALUES ('West')) SELECT wanted_region FROM params;
CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);
INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);
SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;

CREATE TABLE report_choice AS WITH params(wanted_region) AS (VALUES ('East')) SELECT wanted_region FROM params;
CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);
INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);
SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_region) AS (VALUES ('West')) SELECT wanted_region FROM params;2CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);
    values this step1 rowchoice

  2. sales ← 6 rows

    2CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);3INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);4SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;
    values this step6 rowssales

  3. result ← 3 rows

    3INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);4SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;
    values this step3 rowsresult

  1. choice ← 1 row

    1CREATE TABLE report_choice AS WITH params(wanted_region) AS (VALUES ('East')) SELECT wanted_region FROM params;2CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);
    values this step1 rowchoice

  2. sales ← 6 rows

    2CREATE TABLE sales (region TEXT, month TEXT, amount INTEGER);3INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);4SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;
    values this step6 rowssales

  3. result ← 3 rows

    3INSERT INTO sales VALUES ('West', '2026-01', 40), ('West', '2026-02', 55), ('West', '2026-03', 30), ('East', '2026-01', 25), ('East', '2026-02', 35), ('East', '2026-03', 50);4SELECT month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales WHERE region = (SELECT wanted_region FROM report_choice) ORDER BY month;
    values this step3 rowsresult
window aggregate `SUM(...) OVER` adds a total without collapsing detail rows.
partition `PARTITION BY region` keeps each region's running total separate.
frame `UNBOUNDED PRECEDING` through the current row defines the cumulative window.