Tuesday 29 May 2012

Running Total


When I am reviewing a stored procedure, I find that to calculate the value of running total the stored proc contains CURSOR… WHILE Loop etc and contain complex queries.
In this article I am demonstrating a simple way to finding the running total in single select statements.
Let's start it with an example

-- Create Temp Table
CREATE TABLE #Tmp_Prod
       (ProdId    INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
        ProdName  VARCHAR(50)  NOT NULL,
        ProdPrice DECIMAL(20,2)NOT NULL)
       
-- Inserting data into Temp Table
INSERT INTO #Tmp_Prod       
            (ProdName, ProdPrice)
VALUES ('Tooth Paste', 50),
         ('Tooth Brush', 20),
         ('Mouth Wash', 150),                       
         ('Face wash', 250),
         ('Saving Cream', 150),           
         ('Saving Brush', 25)  
                 
-- Display Records
SELECT * FROM #Tmp_Prod               

-- Result Set
ProdId      ProdName          ProdPrice
1           Tooth Paste       50.00
2           Tooth Brush       20.00
3           Mouth Wash        150.00
4           Face wash         250.00
5           Saving Cream      150.00
6           Saving Brush      25.00

------------------------------------------
-- Making the Running total by EXAMPLE-1
------------------------------------------
SELECT  a.ProdId, a.ProdName, a.ProdPrice,
        (SELECT SUM(b.ProdPrice)
         FROM   #Tmp_Prod  b
         WHERE  b.ProdId <= a.ProdId) As Running_Total
FROM   #Tmp_Prod  a
ORDER BY a.ProdId

-- Result Set
ProdId      ProdName          ProdPrice   Running_Total
1           Tooth Paste       50.00       50.00
2           Tooth Brush       20.00       70.00
3           Mouth Wash        150.00      220.00
4           Face wash         250.00      470.00
5           Saving Cream      150.00      620.00
6           Saving Brush      25.00       645.00

StmtText [ Execution plan information in Text ]

  |--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[ProdId]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [a]), ORDERED FORWARD)
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END))
                 |--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1013]=SUM([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [b].[ProdPrice])))
                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [b]), SEEK:([b].[ProdId] <= [tempdb].[dbo].[#Tmp_Prod].[ProdId] as [a].[ProdId]) ORDERED FORWARD)

------------------------------------------
-- Making the Running total by EXAMPLE-2
-----------------------------------------


SELECT a.ProdId, a.ProdName, a.ProdPrice,
       SUM(b.ProdPrice)As Running_Total
FROM   #Tmp_Prod a
       CROSS JOIN #Tmp_Prod b
WHERE (b.ProdId <= a.ProdId)
GROUP BY a.ProdId, a.ProdName, a.ProdPrice
ORDER BY a.ProdId

-- Result Set
ProdId      ProdName          ProdPrice   Running_Total
1           Tooth Paste       50.00       50.00
2           Tooth Brush       20.00       70.00
3           Mouth Wash        150.00      220.00
4           Face wash         250.00      470.00
5           Saving Cream      150.00      620.00
6           Saving Brush      25.00       645.00

StmtText [ Execution plan information in Text ]

|--Stream Aggregate(GROUP BY:([a].[ProdId]) DEFINE:([Expr1004]=SUM([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [b].[ProdPrice]), [a].[ProdName]=ANY([tempdb].[dbo].[#Tmp_Prod].[ProdName] as [a].[ProdName]), [a].[ProdPrice]=ANY([tempdb].[dbo].[#Tmp_Prod].[ProdPrice] as [a].[ProdPrice])))
       |--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[#Tmp_Prod].[ProdId] as [b].[ProdId]<=[tempdb].[dbo].[#Tmp_Prod].[ProdId] as [a].[ProdId]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [a]), ORDERED FORWARD)
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Tmp_Prod] AS [b]))

Here both Example-1 and Example-2 provide the same result set and solve the problem, but I personally preferred the Example-2 by using the CROSS join as it gives us better performance then Example-1.
Hope you like it.

Posted by: MR. JOYDEEP DAS

2 comments:

  1. IT luks gr8... but wht i think is tht u hav assumed the product id to be just an incrementing integer value which is usually not the case as product may b identified using alphanumeric strings...

    ReplyDelete
    Replies
    1. Thanks "Kaus"
      Please try with alpha numeric value in product ID. It works fine. Please try it.

      Delete