Running Totals in SQL

A running total answers "how much so far?" at every row: the balance after each transaction, revenue to date, the count of signups since launch. In SQL it's an ordinary aggregate used as a window, with an ORDER BY inside OVER.

select entry_date,
       amount,
       sum(amount) over (order by entry_date, entry_id) as balance
from ledger

How the sum accumulates

With an ORDER BY in the window, sum() adds up the rows from the start of the window to the current one. Row 1 sees only itself, row 2 sees rows 1 and 2, and so on, so the last row holds the grand total. Every row stays in the result, unlike a GROUP BY total.

The tie-breaker in the window matters. If two rows share an order_date and the window orders by date alone, SQL's default frame treats them as one step and both show the total after both. Ordering by order_date, order_id gives every row its own step. Lesson 19.4 · Window frames explains why.

Restarting for each group

Add PARTITION BY and the total starts again for each group. Here each customer's spend accumulates separately:

with order_revenue as (
  select o.customer_id,
         o.order_id,
         o.order_date,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  where o.status = 'shipped'
  group by o.customer_id, o.order_id, o.order_date
)
select customer_id,
       order_date,
       revenue,
       sum(revenue) over (
         partition by customer_id
         order by order_date, order_id
       ) as customer_running_revenue
from order_revenue
where customer_id in (1, 10)
order by customer_id, order_date

Customer 1 reaches 98 after two shipped orders, and customer 10's total starts again at 57.