How to calculate running total in SQL Server?

Problem

You need to calculate a running total in SQL Server and provide a solution that is also reproducible in MySQL.

Input

To illustrate this problem, let’s create a sample input table named “Sales” with some data. The table will have two columns: “Date” and “Amount.”

Date Amount
2023-09-01 100.00
2023-09-02 75.50
2023-09-03 50.25
2023-09-04 120.75
2023-09-05 90.00

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

Date Amount Total
2023-09-01 100.00 100.00
2023-09-02 75.50 175.50
2023-09-03 50.25 225.75
2023-09-04 120.75 346.50
2023-09-05 90.00 436.50

Solution 1:

Using ROW_NUMBER()

    WITH RunningTotal AS (
        SELECT
            Date,
            Amount,
            SUM(Amount) OVER (ORDER BY Date) AS Total
        FROM
            Sales
    )

Explanation:

We create a CTE named “RunningTotal” that calculates the running total by using the SUM() window function.

The SUM(Amount) OVER (ORDER BY Date) part calculates the running total by summing the “Amount” column for each row, ordering the rows by the “Date” column.

Finally, we select all columns from the “RunningTotal” CTE to see the running total for each row in the “Sales” table.
This query will give you a result set with three columns: “Date,” “Amount,” and “Total,” where “Total” represents the running total at each date.

Solution 2:

By using a subquery with a JOIN

In this approach, we condider the top 1 row to be the row with maximum score.

SELECT
    Date,
    Amount,
    (SELECT SUM(Amount) 
     FROM Sales AS S 
     WHERE S.Date <= Sales.Date) AS Total
FROM
    Sales;

Explanation:

In this query, we select the “Date” and “Amount” columns from the “Sales” table.

The subquery (SELECT SUM(Amount) FROM Sales AS S WHERE S.Date <= Sales.Date) calculates the running total for each row. It sums the “Amount” column for all rows in the “Sales” table where the “Date” is less than or equal to the “Date” of the current row.

The result set will include three columns: “Date,” “Amount,” and “Total,” where “Total” represents the running total at each date.

Solution 3:

Using a Join

SELECT
    s1.Date,
    s1.Amount,
    SUM(s2.Amount) AS Total
FROM
    Sales s1
JOIN
    Sales s2
ON
    s1.Date >= s2.Date
GROUP BY
    s1.Date, s1.Amount
ORDER BY
    s1.Date;

Explanation:

In this query, we perform a self-join on the “Sales” table (aliased as s1 and s2) based on the condition that the s1.Date is greater than or equal to s2.Date. This join helps us combine each row with all previous rows, including itself.

We then use the SUM(s2.Amount) function to calculate the running total. This aggregation sums the “Amount” from all rows with dates less than or equal to the date of the current row (s1.Date).

We use GROUP BY to group the results by “Date” and “Amount” to avoid duplication, and finally, we use ORDER BY to order the results by “Date.”

Recommended Courses

  1. SQL for Data Science – Level 1
  2. SQL for Data Science – Level 2
  3. SQL for Data Science – Level 3

Recommended Tutorial

  1. Introduction to SQL
  2. SQL Window Functons – Made Simple and Easy
  3. SQL Subquery

More SQL Questions

  1. How to select only rows with max value on a column?
  2. How to transpose columns to rows in SQL?
  3. How to select first row in each GROUP BY group?

Course Preview

Machine Learning A-Z™: Hands-On Python & R In Data Science

Free Sample Videos:

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science