Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server
NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query. So instead of using the technique I used in this post you should use technique mentioned in my new post - How to Calculating Running Totals
In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -
- Running Total for all records.
- Running Total on each Group of Records.
- Running Total on each Date.
Here is our sample table :
ShipId | OrderId | ShipDate | Quantity |
1 | 1 | 2009-02-01 12:06:16.820 | 10 |
2 | 1 | 2009-02-02 16:16:16.820 | 15 |
3 | 2 | 2009-02-01 13:26:16.820 | 20 |
4 | 2 | 2009-02-03 17:40:16.820 | 12 |
5 | 2 | 2009-02-04 13:05:16.820 | 15 |
6 | 3 | 2009-02-03 14:25:16.820 | 25 |
7 | 3 | 2009-02-04 15:50:16.820 | 50 |
8 | 3 | 2009-02-05 19:40:16.820 | 30 |
9 | 4 | 2009-02-06 15:30:16.820 | 20 |
10 | 4 | 2009-02-07 16:20:16.820 | 10 |
11 | 5 | 2009-02-05 17:05:16.820 | 35 |
Create Sample Data:
-- Create Table INSERT INTO Sales SELECT |
Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :
SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal |
Here is the result returned by above query :
ShipId | OrderId | ShipDate | Quantity | RunningTotal |
1 | 1 | 2009-02-01 12:06:16.820 | 10 | 10 |
2 | 1 | 2009-02-02 16:16:16.820 | 15 | 25 |
3 | 2 | 2009-02-01 13:26:16.820 | 20 | 45 |
4 | 2 | 2009-02-03 17:40:16.820 | 12 | 57 |
5 | 2 | 2009-02-04 13:05:16.820 | 15 | 72 |
6 | 3 | 2009-02-03 14:25:16.820 | 25 | 97 |
7 | 3 | 2009-02-04 15:50:16.820 | 50 | 147 |
8 | 3 | 2009-02-05 19:40:16.820 | 30 | 177 |
9 | 4 | 2009-02-06 15:30:16.820 | 20 | 197 |
10 | 4 | 2009-02-07 16:20:16.820 | 10 | 207 |
11 | 5 | 2009-02-05 17:05:16.820 | 35 | 242 |
Case II: Running Total on each Group of Records.
Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :
SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.ShipId >= B.ShipId AND A.OrderId = B.OrderId GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity ORDER BY OrderId |
And the Output :
ShipId | OrderId | ShipDate | Quantity | RunningTotal |
1 | 1 | 2009-02-01 12:06:16.820 | 10 | 10 |
2 | 1 | 2009-02-02 16:16:16.820 | 15 | 25 |
3 | 2 | 2009-02-01 13:26:16.820 | 20 | 20 |
4 | 2 | 2009-02-03 17:40:16.820 | 12 | 32 |
5 | 2 | 2009-02-04 13:05:16.820 | 15 | 47 |
6 | 3 | 2009-02-03 14:25:16.820 | 25 | 25 |
7 | 3 | 2009-02-04 15:50:16.820 | 50 | 75 |
8 | 3 | 2009-02-05 19:40:16.820 | 30 | 105 |
9 | 4 | 2009-02-06 15:30:16.820 | 20 | 20 |
10 | 4 | 2009-02-07 16:20:16.820 | 10 | 30 |
11 | 5 | 2009-02-05 17:05:16.820 | 35 | 35 |
Case III: Running Total on each Date.
Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :
SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal |
And the output :
ShipId | OrderId | ShipDate | Quantity | RunningTotal |
1 | 1 | 2009-02-01 12:06:16.820 | 10 | 10 |
3 | 2 | 2009-02-01 13:26:16.820 | 20 | 30 |
2 | 1 | 2009-02-02 16:16:16.820 | 15 | 15 |
6 | 3 | 2009-02-03 14:25:16.820 | 25 | 25 |
4 | 2 | 2009-02-03 17:40:16.820 | 12 | 37 |
5 | 2 | 2009-02-04 13:05:16.820 | 15 | 15 |
7 | 3 | 2009-02-04 15:50:16.820 | 50 | 65 |
11 | 5 | 2009-02-05 17:05:16.820 | 35 | 35 |
8 | 3 | 2009-02-05 19:40:16.820 | 30 | 65 |
9 | 4 | 2009-02-06 15:30:16.820 | 20 | 20 |
10 | 4 | 2009-02-07 16:20:16.820 | 10 | 10 |
Hopefully these examples will help you understand different methods that can be used to calculate running totals.
- Mangal Pardeshi