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.
