Baldwin Apps

Posted on Jun 20

SQL Pattern Series #7: The Running Total Pattern

#database #dataengineering #sql #tutorial

Seeing how values build over time

SQL Pattern Series #7 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a running total is
How SUM() works as a window function
Why ORDER BY matters inside OVER()
When to use running totals in reports and dashboards

Most reports show totals.

But sometimes a single total is not enough.

You may also need to see how that total builds over time.

For example:

What were cumulative sales by day?

or:

How did the balance grow after each transaction?

That is where the Running Total Pattern becomes useful.

The Problem

Imagine a daily sales table:

Region	OrderDate	DailyTotal
West	2024-01-01	100
West	2024-01-02	150
West	2024-01-03	75
East	2024-01-01	200
East	2024-01-02	50

A regular total can tell you the final amount.

But it does not show the path.

A running total shows how the value accumulates row by row.

The Running Total Pattern

The Running Total Pattern uses a window function to calculate a cumulative total.

The common structure looks like this:

SUM(column) OVER (
    PARTITION BY column
    ORDER BY column
)

The SUM() calculates the total.

The OVER() clause defines the window.

PARTITION BY restarts the calculation for each group.

ORDER BY controls the sequence in which values accumulate.

Example

SELECT
    Region,
    OrderDate,
    DailyTotal,
    SUM(DailyTotal) OVER (
        PARTITION BY Region
        ORDER BY OrderDate
    ) AS RunningTotal
FROM DailySales;

This query calculates a running total for each region.

For the West region, the running total builds across West rows.

For the East region, the running total starts over and builds across East rows.

Example Result

Region	OrderDate	DailyTotal	RunningTotal
West	2024-01-01	100	100
West	2024-01-02	150	250
West	2024-01-03	75	325
East	2024-01-01	200	200
East	2024-01-02	50	250

The running total shows the cumulative value after each row.

Instead of only seeing the final total, you can see how the total grows.

Why This Pattern Matters

Running totals are useful because they show progression.

They help answer questions like:

How much has accumulated so far?
When did the total pass a threshold?
How quickly is value building?
How does one group compare to another over time?

This makes the pattern useful for:

sales reporting
finance reports
inventory tracking
account balances
progress dashboards

PARTITION BY Controls the Reset

One important part of the pattern is:

PARTITION BY Region

This tells SQL:

Calculate a separate running total for each region.

Without PARTITION BY, SQL calculates one running total across the entire result set.

That may be correct in some cases.

But if you need one running total per customer, region, account, product, or category, PARTITION BY defines where the total resets.

ORDER BY Controls the Story

The other important part is:

ORDER BY OrderDate

A running total depends on sequence.

If the rows are ordered differently, the running total changes.

That means the ORDER BY inside the window function is not just formatting.

It defines the logic of the calculation.

A Note on Ties

If two rows have the same OrderDate, the database may not have a predictable order between them.

For example:

ORDER BY OrderDate

may not be enough if multiple rows share the same date.

If the result must be deterministic, add a tiebreaker:

SUM(DailyTotal) OVER (
    PARTITION BY Region
    ORDER BY OrderDate,
             SaleID
) AS RunningTotal

Now SQL has a stable order when two rows have the same date.

Running Total vs Moving Sum

The Running Total Pattern is closely related to the Moving Sum Pattern.

But they answer different questions.

A running total asks:

How much has accumulated so far?

A moving sum asks:

What is the total over the most recent window?

Running totals keep building.

Moving sums slide forward.

Both are useful, but they tell different stories.

When I Reach for This Pattern

I typically use the Running Total Pattern when:

values accumulate over time
I need cumulative totals
each row should show progress so far
totals need to restart by group

Examples include:

cumulative sales by region
running account balances
inventory movement over time
cumulative signups
progress toward a goal

Key Takeaway

Regular totals show the final number.

Running totals show how the number builds.

The Running Total Pattern helps answer:

How much has accumulated so far?

That makes it one of the most useful patterns for reports, dashboards, and trend analysis.