Introduction

A cumulative sum column shows a running total as rows progress in a defined order. In modern MySQL, the cleanest solution is a window function with SUM() OVER (...); in older versions, you can simulate the same behavior with user-defined variables, but that approach is less robust and more version-sensitive.

Core Sections

Use a window function in MySQL 8+

If you are on MySQL 8 or later, use SUM() as a window function. The crucial part is the ORDER BY, because a cumulative total only makes sense relative to a row order.

1SELECT
2    id,
3    created_at,
4    amount,
5    SUM(amount) OVER (ORDER BY created_at, id) AS running_total
6FROM payments
7ORDER BY created_at, id;

This returns each row together with the total of amount from the beginning of the ordered result set up to the current row.

Including a tie-breaker such as id is often important. If multiple rows share the same timestamp and you do not break ties explicitly, the running order may be ambiguous.

Partition when totals should reset per group

Often the running total should restart for each customer, account, or category. In that case, use PARTITION BY.

1SELECT
2    customer_id,
3    created_at,
4    amount,
5    SUM(amount) OVER (
6        PARTITION BY customer_id
7        ORDER BY created_at, id
8    ) AS customer_running_total
9FROM payments
10ORDER BY customer_id, created_at, id;

This keeps one cumulative total per customer rather than one global total.

Understand the frame behavior

MySQL window functions can use different frames. For running totals, the usual intent is “from the first row in the partition up to the current row.” MySQL’s default with an ordered window often aligns with that intent, but being explicit can make the query easier to read.

1SELECT
2    id,
3    amount,
4    SUM(amount) OVER (
5        ORDER BY id
6        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
7    ) AS running_total
8FROM payments;

That wording makes the running-total behavior obvious to future readers.

Older MySQL versions: variables as a fallback

Before MySQL 8, a common workaround used session variables.

1SET @running_total := 0;
2
3SELECT
4    id,
5    amount,
6    (@running_total := @running_total + amount) AS running_total
7FROM payments
8ORDER BY id;

This can work, but it is more brittle. Query evaluation order and optimization details can make variable-based solutions harder to reason about than window functions. Use the window-function version whenever the server supports it.

Make sure the ordering column is meaningful

A cumulative sum without a stable order is not well-defined. If you order by a non-unique column such as created_at alone, tied rows can produce surprising totals depending on execution details.

The safe pattern is to order by:

• the business column that defines progression
• plus a unique tie-breaker column

That makes the running total deterministic. It also keeps review and troubleshooting simpler, because the cumulative progression no longer depends on implicit engine choices for tied rows.

Common Pitfalls

• Forgetting ORDER BY in the window definition, which turns the query into a plain partition total rather than a cumulative total.
• Using a non-deterministic ordering column and getting unstable running totals when multiple rows tie.
• Reaching for user-defined variables in MySQL 8+ when a clearer window-function solution is available.
• Confusing “running total per group” with “running total across the whole table” and omitting PARTITION BY when it is needed.
• Assuming the displayed outer ORDER BY alone controls the cumulative logic when the real ordering for the running sum is inside the window clause.

Summary

• In MySQL 8+, use SUM(amount) OVER (ORDER BY ...) for cumulative totals.
• Add PARTITION BY when the running total should restart for each group.
• Use a stable ordering with a tie-breaker column so results stay deterministic.
• User-defined variables are a fallback for older MySQL versions, not the preferred modern solution.
• The window clause defines the cumulative logic, so order it carefully.