Tuesday, February 3, 2009

Calculating Running Totals in SQL

 

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
CREATE TABLE Sales
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT )
GO
-- Load Sample Data

INSERT INTO Sales SELECT
1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT
2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT
3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT
4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT
5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT
6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT
7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT
8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT
9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT
10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT
11,5, 'Feb 05 2009 05:05:16:820PM',35


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
FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity

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
FROM Sales A INNER JOIN Sales B
ON A.shipDate >= B.Shipdate
AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0)
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY A.ShipDate

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

Posted by Unknown at Tuesday, February 03, 2009
Labels: , ,

2 comments:

  1. Anonymous4:27:00 PM

    select abc.item_name,sum(amount) as total from (select a.item_id,d.applicablefrom,a.item_name,a.final_item_status,d.rate,c.item_name as sub_item_name,
    b.sub_item_qty as itemqty, (b.sub_item_qty * d.rate)as amount
    from tblitem_master a,tblitem_master c,tblitem_bom_master b,
    (select rate,applicablefrom,itemid from tblperiodrates where applicablefrom= (select max(applicablefrom)
    from tblperiodrates where applicablefrom<='2005-5-18'))as d
    where a.item_id = b.item_id And
    b.sub_item_id = c.item_id and
    b.sub_item_id = d.itemid and
    a.final_item_status='f') as abc group by abc.item_name

    ReplyDelete
  2. Anonymous4:29:00 PM

    select abc.item_name,sum(amount) as total from (select a.item_id,d.applicablefrom,a.item_name,a.final_item_status,d.rate,c.item_name as sub_item_name,
    b.sub_item_qty as itemqty, (b.sub_item_qty * d.rate)as amount
    from tblitem_master a,tblitem_master c,tblitem_bom_master b,
    (select rate,applicablefrom,itemid from tblperiodrates where applicablefrom= (select max(applicablefrom)
    from tblperiodrates where applicablefrom<='2005-5-18'))as d
    where a.item_id = b.item_id And
    b.sub_item_id = c.item_id and
    b.sub_item_id = d.itemid and
    a.final_item_status='f') as abc group by abc.item_name

    ReplyDelete

Subscribe to: Post Comments (Atom)