Calculating Running total

Here in this Blog I have shown how to calculate Running totals and compared two ways of caculating it.

I have tried with INNER JOIN and CTE. Both these methods brings the correct result,
But I could clearly see that CTE took only 27% of the total execution time compared to INNER JOIN, INNER JOIN took remaining 73% to bring the same result.

I have provided the Example below....

Here We Go...!!!!

sample table for testing.

CREATE TABLE PRO (ID INT IDENTITY(1,1),PRO_NAME VARCHAR(100),PRO_QTY INT)

INSERT PRO VALUES ('A',10)
INSERT PRO VALUES ('B',20)
INSERT PRO VALUES ('C',30)
INSERT PRO VALUES ('D',20)
INSERT PRO VALUES ('E',10)
INSERT PRO VALUES ('F',50)
INSERT PRO VALUES ('G',5)

METHOD 1:
using INNER JOIN

SELECT A.ID,A.PRO_NAME,A.PRO_QTY,SUM(B.PRO_QTY) as RUNNING_TOTAL
FROM PRO A
INNER JOIN PRO B
ON (B.ID <= A.ID)
GROUP BY A.ID,A.PRO_NAME,A.PRO_QTY

METHOD 2:
CTE

;WITH CTE(ID,PRO_NAME,PRO_QTY,RUNNING_TOTAL)
AS
(
SELECT ID,PRO_NAME,PRO_QTY,PRO_QTY AS RUNNING_TOTAL FROM PRO
WHERE ID =1
UNION ALL
SELECT P.ID,P.PRO_NAME,P.PRO_QTY,P.PRO_QTY+C.RUNNING_TOTAL FROM PRO P
JOIN CTE C ON C.ID+1 = P.ID
)
SELECT * FROM CTE

DROP TABLE PRO

Results from both methods.



Execution Plan of both Methods . click on the Image to view