Adding Running Totals and Cumulative Sums

Your transaction log has a row for every payment, refund, and fee. Each row has an amount. Your accountant wants a balance column - the running total after each transaction. Row 1 shows $500. Row 2 adds $200, so the balance is $700. Row 3 subtracts $50 for a fee, balance drops to $650. And so on for 10,000 rows.

In Excel, you'd put =B1 in C1 and =C1+B2 in C2, then drag it down. It works until someone inserts a row, sorts the data, or the file gets big enough that recalculation takes forever. The formula approach is brittle.

In ExploreMyData, running totals use a window function. No formulas to drag. No cell references to break.

Basic running total

Click the green + in the Pipeline panel and select Window Function from the Aggregate group. The function dropdown holds six entries; the one you want is the last, RUNNING TOTAL (cumulative sum). There is no plain SUM in that list. Choose amount as the column (the field only offers numeric columns) and set Order by to transaction_date.

Now look at the direction switch that appears next to Order by. It defaults to Largest first, and for a balance column that default is wrong: it accumulates from the newest transaction backwards. Flip it to Smallest first so the total builds oldest to newest, the way a bank statement reads. Name the output running_balance; leave the name blank and you get running_total_result.

ExploreMyData generates:

SUM("amount") OVER (ORDER BY "transaction_date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "running_balance"

That ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the key part. It tells DuckDB: for each row, sum every amount from the first row of the ordered window up to and including this one. That is your running total. Note the ASC, which is what "Smallest first" produced. Had you left the switch on Largest first it would say DESC and the first row of the window would be your most recent transaction.

Why order matters

A running total without an order is meaningless. "The cumulative sum up to this row" only makes sense if there's a defined sequence, which is why the ORDER BY carries so much weight here. Leave Order by empty and the frame clause disappears entirely: you get SUM("amount") OVER (), the grand total of the whole column repeated on every row. Handy when that is what you wanted, confusing when it isn't.

Usually you order by a date or timestamp. But it could be an ID column, a sequence number, or anything that defines the logical order of your data. If your transaction log has a transaction_id column that increments, that works too.

What if two transactions share a date? Here is the honest answer: the panel cannot help you. Order by is a single-select dropdown with one direction switch, and there is no second field for a tiebreaker. DuckDB will process same-date rows in whatever order they happen to arrive, and the intermediate balances on those rows are arbitrary. The final total is still right, but the sequence through the tie is not something you can rely on.

If the within-day sequence genuinely matters, build one sortable column and order by that. A Combine Columns step (Columns group) will concatenate the date chip, a literal separator, and the transaction_id chip into a single text key. The catch is that it sorts as text, so it only behaves if both parts are fixed width: an id of 10 sorts before 9 unless you pad it. Otherwise, accept the tie order and don't read meaning into it.

Running total per group

Sometimes you want separate running totals. Revenue accumulating per sales rep. Inventory moving per warehouse. Payments accumulating per customer.

Same step, one extra field. Set Partition by to customer_id and each customer gets their own running total that starts from zero. Keep Order by on the date with Smallest first.

SUM("amount") OVER (PARTITION BY "customer_id" ORDER BY "transaction_date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "customer_running_total"

Customer A's running total accumulates independently from Customer B's. The partition creates a separate window for each group.

Cumulative count instead of sum

Running totals don't have to sum a monetary value. Sometimes you want a cumulative count: how many orders has this customer placed up to this date?

There is no COUNT in the Window Function dropdown, and you don't need one. ROW_NUMBER is the running count of rows so far. Pick it as the function, partition by customer_id, order by order_date with Smallest first, and name the output order_number. The Column field vanishes when you select ROW_NUMBER, which is expected: it counts positions rather than reading a value. You get a column that starts at 1 for each customer's first order and climbs by one per row.

ROW_NUMBER() OVER (PARTITION BY "customer_id" ORDER BY "order_date" ASC) AS "order_number"

Direction matters just as much here. On Largest first, order number 1 lands on the customer's most recent order instead of their first.

Dealing with NULL amounts

If your amount column has NULL values, the sum skips them. That is usually what you want: a refund that hasn't been processed yet shouldn't move the running balance. But if NULLs represent zero-value transactions, fill them first with Fill Missing from the Data group, method literal, fill value 0.

Practical uses

Running totals show up everywhere:

• Revenue tracking: cumulative revenue by month for the fiscal year
• Inventory: stock level after each shipment in and out
• Bank reconciliation: running balance across deposits and withdrawals
• Support tickets: cumulative open count over time

The recipe barely changes between them. Pick the numeric column, set Order by to your date column, switch the direction to Smallest first, and add a partition if you want one total per group. That single step behaves the same on 50 rows as it does on 50,000, and nobody has to drag a formula down a spreadsheet to keep it current.

Start building running totals →