Forum

Forums
SQL
Interview Questions...
Best Way to Find Ru...
 
Share:
Notifications
Clear all

Best Way to Find Running Total in SQL Without Using Window Functions

 
Interview Questions & Answers
Last Post by SQL Admin 8 months ago
1 Posts
1 Users
0 Reactions
1,354 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jul 24, 2025 2:36 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Best Way to Find Running Total in SQL Without Using Window Functions

Calculating a running total (also known as a cumulative sum) is a very common SQL operation in finance, sales reports, inventory management, and dashboards. While modern databases like PostgreSQL, SQL Server, and Oracle support SUM() OVER (ORDER BY ...), not every system or developer uses window functions.

This post explains how to calculate running totals without using window functions, making it ideal for MySQL (older versions), SQLite, and systems with basic SQL support.

What Is a Running Total in SQL?

A running total is the cumulative sum of a column over a sequence of rows. For example, if you have sales by date, a running total will give you the progressive total after each day.

Sample Table:

CREATE TABLE sales (
   sale_date DATE,
   amount INT
);
sale_dateamount
2024-01-01100
2024-01-02150
2024-01-03200

Expected running total:

sale_dateamountrunning_total
2024-01-01100100
2024-01-02150250
2024-01-03200450

Method 1: Using Correlated Subquery (Works Everywhere)

SELECT s1.sale_date, s1.amount,
   (SELECT SUM(s2.amount)
    FROM sales s2
    WHERE s2.sale_date <= s1.sale_date) AS running_total
FROM sales s1
ORDER BY s1.sale_date;

This query does not require any window function and works even in basic SQL engines. It works by summing all rows up to the current date for each record.

Method 2: Using Variables in MySQL

For older MySQL versions (before 8.0), you can simulate running total with user-defined variables:

SELECT sale_date, amount,
   @running_total := @running_total + amount AS running_total
FROM sales, (SELECT @running_total := 0) AS vars
ORDER BY sale_date;

This is very fast and useful for lightweight reporting where window functions aren’t available.

Use Cases Where Running Totals Are Needed

  • Cumulative sales tracking

  • Account balance progression

  • Step-wise total of test scores or marks

  • Inventory balance tracking over time

  • Website traffic trend monitoring

Performance Tip

While correlated subqueries work universally, they can be slow for large datasets. Always test performance with indexes on the date column. For large tables, window functions or temporary tables may be better.

Summary Table

SQL EngineMethod Used
MySQL (old)Variables (@var :=)
SQL ServerSubquery or Window
OracleSubquery or SUM() OVER()
PostgreSQLAny method works
SQLiteCorrelated Subquery

 


Topic Tags
sql running total without cumulative sum sql witho mysql running total quer sql running total exampl running total subquery

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  sql running total without (1) , cumulative sum sql witho (1) , mysql running total quer (1) , sql running total exampl (1) , running total subquery (1) ,
Share: