1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

A SQL running total adds each row’s value to the sum of all earlier rows. The modern way is one window function: SUM(amount) OVER (ORDER BY day, id). This guide shows per-group totals, daily cumulative sums, moving sums and percentages, plus the tie trap that silently gives wrong numbers.

9 min read
SQL running total explained

Key takeaways

  • A SQL running total is SUM(col) OVER (ORDER BY ...); add PARTITION BY to restart it for each group.
  • Order by a unique combination such as day, id. Ordering by a non-unique day alone gave tied rows the same total in our test.
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW turns it into a 3-row moving sum.
  • On 20,000 rows the window function took 22 ms; the old correlated subquery took almost 8 seconds.

Cumulative revenue, account balances, stock levels, burn-down charts and “progress towards target” all need the same calculation: add up everything so far. For years this meant awkward self joins or subqueries. Modern SQL does it with one window function. We ran every query below in SQLite 3.50.4; the syntax is standard and works in PostgreSQL, MySQL 8+, SQL Server 2012+ and Oracle, although we only executed it in SQLite.

Quick answer

SELECT id, day, amount,
       SUM(amount) OVER (ORDER BY day, id) AS running_total
FROM sales;

The OVER clause turns SUM from a grouping function into a window function. Instead of collapsing rows, it calculates a sum for each row over a “window” of rows: here, every row up to and including the current one in the given order.

Sample data

CREATE TABLE sales(id INTEGER PRIMARY KEY, day TEXT, region TEXT, amount INT);
INSERT INTO sales(day, region, amount) VALUES
 ('2026-11-01','North',100), ('2026-11-01','South',50),
 ('2026-11-02','North',80),  ('2026-11-03','North',120),
 ('2026-11-03','South',70),  ('2026-11-03','North',30),
 ('2026-11-04','South',NULL),('2026-11-05','South',90);

Several rows share the same day on purpose, and one amount is NULL. Real data looks like this, and both details change the results.

The basic running total

id day amount running_total
1 2026-11-01 100 100
2 2026-11-01 50 150
3 2026-11-02 80 230
4 2026-11-03 120 350
5 2026-11-03 70 420
6 2026-11-03 30 450
7 2026-11-04 NULL 450
8 2026-11-05 90 540

Each row adds its amount to the total so far. The last value, 540, equals the grand total, which is a quick sanity check worth automating in tests.

The RANGE tie trap

Here is the mistake almost everyone makes once. Order by the day alone, without the id:

SELECT id, day, amount,
       SUM(amount) OVER (ORDER BY day) AS range_total,
       SUM(amount) OVER (ORDER BY day
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM sales;
id day amount range_total rows_total
1 2026-11-01 100 150 100
2 2026-11-01 50 150 150
4 2026-11-03 120 450 350
5 2026-11-03 70 450 420
6 2026-11-03 30 450 450

With only ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats all rows with the same order value as peers and includes them all, so every row on 1 November showed 150 and every row on 3 November showed 450. The ROWS frame counts physical rows, giving a true row-by-row total, but the order of tied rows is still arbitrary unless you add a tie-breaker.

Tip: always order a running total by something unique, usually the date plus the primary key. That gives the same result with RANGE or ROWS and the same result every time you run it.

RANGE is not wrong; it answers a different question. “Total sales up to the end of each day” is exactly RANGE ordered by day. Just choose it on purpose.

Running total per group

SELECT region, day, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY day, id) AS region_running
FROM sales
ORDER BY region, day, id;
region day amount region_running
North 2026-11-01 100 100
North 2026-11-02 80 180
North 2026-11-03 120 300
North 2026-11-03 30 330
South 2026-11-01 50 50
South 2026-11-03 70 120
South 2026-11-04 NULL 120
South 2026-11-05 90 210

PARTITION BY restarts the total for every region. Use it for per-customer balances, per-account ledgers or per-month totals: PARTITION BY strftime('%Y-%m', day) in SQLite, or DATE_TRUNC('month', day) in PostgreSQL. The same idea drives ranking functions, covered in our RANK vs DENSE_RANK guide.

Daily totals, then a cumulative total

SELECT day, SUM(amount) AS daily,
       SUM(SUM(amount)) OVER (ORDER BY day) AS cumulative
FROM sales GROUP BY day;
day daily cumulative
2026-11-01 150 150
2026-11-02 80 230
2026-11-03 220 450
2026-11-04 NULL 450
2026-11-05 90 540

SUM(SUM(amount)) looks odd, but it reads naturally once you know the order of evaluation: GROUP BY runs first and produces one daily sum per day, then the window function adds those daily sums. This is the query behind most cumulative revenue charts. Grouping is explained in our GROUP BY and HAVING guide.

Moving sums and averages

SELECT id, amount,
       SUM(amount) OVER w AS sum3,
       ROUND(AVG(amount) OVER w, 1) AS avg3
FROM sales
WINDOW w AS (ORDER BY day, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

Changing the frame from “all earlier rows” to “the current row and the two before it” creates a rolling window. Row 4 had a 3-row sum of 250 (rows 2, 3 and 4: 50 + 80 + 120), and row 6 showed 220 with an average of 73.3. Notice row 7: its average was 50.0, because AVG ignores the NULL and divided 100 by two rows, not three. Rolling averages smooth out noisy daily data in dashboards.

Cumulative percent of total

SELECT id, amount,
       ROUND(100.0 * SUM(amount) OVER (ORDER BY day, id)
             / SUM(amount) OVER (), 1) AS pct_cum
FROM sales;

An empty OVER () means “all rows”, so dividing the running total by it gives a cumulative percentage. Our data reached 18.5% after row 1, 64.8% after row 4 and 100.0% at the end. Ordering by amount descending instead gives a Pareto view: how few items make up 80% of the total.

How NULL values behave

SUM skips NULLs, so row 7 simply repeated the previous total, 450, instead of turning the whole total into NULL. That is usually what you want. If a missing amount should count as zero in other calculations, wrap it: SUM(COALESCE(amount, 0)), which gave identical totals here. Be more careful with AVG and COUNT(amount), which also skip NULLs and therefore change the denominator. Our SQL COALESCE guide covers these choices.

The old way: a correlated subquery

SELECT s.id, s.amount,
       (SELECT SUM(amount) FROM sales x
        WHERE x.day < s.day OR (x.day = s.day AND x.id <= s.id)) AS running
FROM sales s ORDER BY day, id;

This gives the same 100, 150, 230, … 540 results and works in very old databases without window functions, such as MySQL 5.7. A self join with GROUP BY is another variation; see our SQL self join guide. Both share the same weakness: for every row, the database sums all earlier rows again.

Speed test: window function vs subquery

We generated 20,000 rows and timed both approaches in SQLite:

Method Time
SUM(v) OVER (ORDER BY id) 21.9 ms
Correlated subquery 7,979 ms

The window function was about 360 times faster, because it keeps a running sum as it walks through the rows once, while the subquery re-adds earlier rows for every single row. The gap grows with every extra row, so on large tables the subquery approach is not just slower but unusable.

Real-world uses

  • Account balance: running total of credits and debits per account, partitioned by account and ordered by transaction time and id.
  • Inventory: stock on hand after each receipt or sale.
  • Targets: cumulative sales against a monthly goal.
  • Burn-down: remaining work after each completed task.
  • Data checks: the final running total must equal the plain SUM, a handy assertion in data tests.

Database support

Window functions with SUM() OVER are available in PostgreSQL, SQL Server 2012 and later, Oracle, MySQL 8.0 and later, MariaDB 10.2 and later, and SQLite 3.25 and later. The named WINDOW clause we used for the moving sum is supported in PostgreSQL, MySQL 8 and SQLite, but not in SQL Server before 2022, where you repeat the OVER (...) instead. More window patterns are in our SQL window functions tutorial.

Common mistakes

  • Ordering by a non-unique column. Ties share a total under RANGE and get arbitrary order under ROWS. Add the primary key.
  • Forgetting ORDER BY inside OVER. SUM(x) OVER () gives the grand total on every row, not a running total.
  • Confusing the outer ORDER BY with the window ORDER BY. The window order defines the calculation; the outer order only sorts the output.
  • Filtering with WHERE after the fact. WHERE runs before the window, so it changes the totals. Filter in an outer query if you only want to display some rows.
  • Ignoring NULLs in averages. Decide whether missing means zero.

Final thoughts

A SQL running total is one of the clearest wins of window functions: one readable line replaces slow self joins and subqueries. Order by a unique key, add PARTITION BY for per-group totals, change the frame for moving sums, and divide by SUM() OVER () for percentages. Check the final row against the grand total, and your cumulative reports will be both fast and right.

Frequently asked questions

How do I calculate a running total in SQL?

Use a window function: SUM(amount) OVER (ORDER BY date_column, id). Each row gets the sum of its own value and all earlier rows in that order.

How do I reset a running total for each group?

Add PARTITION BY inside the OVER clause, for example SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, id).

Why do rows with the same date show the same running total?

With only ORDER BY, the default frame is RANGE, which includes all rows with the same order value. Order by a unique combination such as date and id, or use a ROWS frame.

How do I calculate a running total without window functions?

Use a correlated subquery or a self join that sums all earlier rows. It works in old databases but is far slower; in our test it took about 8 seconds on 20,000 rows.

How do I calculate a moving sum in SQL?

Use a ROWS frame, for example SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) for a three-row moving sum.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →