Running Total with Window Functions (and a Reset Trick)

🗃️ Code Snippet — June 27, 2026

Running Total with Window Functions (and a Reset Trick)

Need a cumulative sum over a column? Financial dashboards, inventory tracking, session analytics — SUM() OVER() handles it natively in PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server.

Basic running total:

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

Per-category running total (resets per group):

SELECT
    category, transaction_date, amount,
    SUM(amount) OVER (
        PARTITION BY category
        ORDER BY transaction_date, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS category_running_total,
    SUM(amount) OVER () AS category_grand_total
FROM transactions
ORDER BY category, transaction_date, id;

Session-based accumulator:

SELECT
    user_id, event_time, duration_ms,
    SUM(duration_ms) OVER (
        PARTITION BY user_id, session_id
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_elapsed_ms
FROM user_events
WHERE event_type IN ('page_view', 'click', 'api_call')
ORDER BY user_id, session_id, event_time;

Pro tip: Always add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly — it's the default for ORDER BY windows but being explicit prevents subtle bugs when defaults change. To get a "remaining" total, mirror with ORDER BY id DESC.

💡 Window functions replace entire application-side accumulation loops with a single query.