Calculating Running Totals

In this lesson, you will learn how to compute a cumulative sum that grows row-by-row as you move through your result set.

The Concept of Cumulative Growth

A running total (or cumulative sum) is a value that keeps a "memory" of previous rows. Imagine you are tracking a bank account balance: instead of just seeing your individual deposits, you want to see the total balance after each specific transaction. To do this in SQL, we need a way to look at the current row and every row that came before it.

We achieve this using a "Window Function." While we have used basic aggregates like SUM() to collapse data into a single scalar value, here we want to keep the rows intact while calculating a sum that resets or grows based on our sorting.

The Syntax

To calculate a running total, we use the OVER clause combined with an ORDER BY statement inside the aggregate function:

SELECT 
    transaction_date, 
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_balance
FROM transactions;

How It Works

1. SUM(amount): This tells SQL which column we want to accumulate.
2. OVER: This tells SQL we are performing this calculation over a "window" of rows rather than the entire table at once.
3. ORDER BY transaction_date: This is the most critical part. It defines the "sequence" of the calculation. SQL will start with the first row, calculate the sum, then move to the second row and add its amount to the previous total, and continue down the line.

Because we did not use a GROUP BY clause, the SQL engine keeps every original row visible in the result set, appending the cumulative balance as a new column for every single entry. If you have two transactions on the same day, SQL treats them as separate steps in the sequence based on how they appear in the result set.

By mastering this, you can transform static lists of numbers into insightful trends, helping you visualize how values accumulate over time.

Key Takeaway: A running total uses the OVER clause with ORDER BY to maintain a cumulative sum that updates as the database engine processes each row in your defined sequence.