Computing Running Totals

With a window function and an explicit frame. The cumulative sum is SUM over the window from the start of the partition up to the current row, which is what ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says exactly.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

SELECT id,
       customer_id,
       placed_on,
       amount,
       SUM(amount) OVER (
         ORDER BY placed_on
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY placed_on;

Four rows come back with running totals of 120.00, 460.50, 535.75 and 625.75. The final figure is the total of every order, which is the check that the running total is right.

The frame is the part that needs care. Without the explicit frame, the default for an ordered window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which behaves identically as long as no two rows share the same ORDER BY value. The moment two rows tie — two orders placed on the same day — RANGE treats them as peers and gives both the same running total, including both of their values. ROWS counts physical rows and gives each one its own running total. For a cumulative report, ROWS is almost always what you want, or at least what you want until you decide that same-day orders should share a total.

Add PARTITION BY to restart the running total for each group — PARTITION BY customer_id ORDER BY placed_on gives Ada a running total over her own orders and Grace one over hers, instead of one combined total across the table.

Aggregate to a monthly total first, then run the cumulative sum over the months. Doing it in that order keeps the window function operating on one row per month rather than on every order.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

WITH monthly AS (
  SELECT strftime('%Y-%m', placed_on) AS month,
         ROUND(SUM(amount), 2) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month,
       revenue,
       SUM(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM monthly
ORDER BY month;

Two rows come back. January shows 460.50 with a running total of 460.50, and February shows 165.25 with a running total of 625.75. The final figure matches the sum of every order, which is the sanity check worth running every time.

The two-stage shape matters more than it looks. If you had written the window function directly over the orders table, the running total would have advanced on every order rather than every month, so the January row would have shown 120.00 and then 460.50 as two separate rows. Aggregating first gives one row per period, which is what a monthly report needs.

One caveat about the ordering. The month key is a text value like '2026-01', and it sorts lexicographically, which happens to match chronological order because the format is zero-padded and year-first. If you were grouping by a month name or a number without padding, the running total would accumulate in the wrong order.