Running total without window functions in SQL

Introduction

A running total adds up values step-by-step in order. It helps see how numbers grow over time without using special window functions.

When your database does not support window functions like SUM() OVER().

When you want to calculate cumulative sales day by day.

When you track total points scored by a player after each game.

When you want to show a growing balance in a bank account statement.

When you analyze stepwise totals in a report but have limited SQL features.

Syntax

SQL

SELECT t1.date, t1.amount,
       (SELECT SUM(t2.amount) FROM table_name t2 WHERE t2.date <= t1.date) AS running_total
FROM table_name t1
ORDER BY t1.date;

This uses a subquery to sum all amounts up to the current row's date.

Make sure to order results to see the running total in correct sequence.

Examples

Calculate running total by id assuming id increases with each row.

SQL

SELECT t1.id, t1.value,
       (SELECT SUM(t2.value) FROM sales t2 WHERE t2.id <= t1.id) AS running_total
FROM sales t1
ORDER BY t1.id;

Running total by date for transactions table.

SQL

SELECT t1.transaction_date, t1.amount,
       (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.transaction_date <= t1.transaction_date) AS running_total
FROM transactions t1
ORDER BY t1.transaction_date;

Sample Program

This example creates a sales table, inserts 4 rows, and calculates running total of amount by id.

SQL

CREATE TABLE sales (
  id INT,
  amount INT
);

INSERT INTO sales (id, amount) VALUES
(1, 100),
(2, 200),
(3, 150),
(4, 50);

SELECT t1.id, t1.amount,
       (SELECT SUM(t2.amount) FROM sales t2 WHERE t2.id <= t1.id) AS running_total
FROM sales t1
ORDER BY t1.id;

Important Notes

This method can be slower on large tables because it runs a subquery for each row.

Ensure the column used for ordering (like date or id) uniquely identifies row order.

If duplicates exist in ordering column, running totals may be incorrect or inconsistent.

Summary

Running total adds values step-by-step without special window functions.

Use a subquery to sum all previous rows up to the current one.

Order your results properly to see the running total grow as expected.