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.”
