Calculate Running Total

Hello readers..!

Some time you need to calculate running total of the item. Many people do extra calculation and long methods to accomplish that. Below is the simple logic to calculate running total. 

First create a table that holds item with there price. I m using temporary table. 

CREATE TABLE #amount

(

      RecordId INT IDENTITY(1,1),

      ItemName VARCHAR(20),

      Cost INT

)

Insert some data into the table. 

INSERT INTO #amount VALUES ('Pen', 10),('Pencil', 5), ('Rubber', 2), ('Scale', 2), ('Paper', 10)

Now creating another temporary table (#TotalAmount) from the above table and with extra field named RunningTotal that holds the Running Total of the items.

SELECT *, 0 AS RuningTotal INTO #TotalAmount FROM #amount

Declaring a variable that calculates the total of item.

DECLARE @amountSum INT = 0;

Update table like below 

UPDATE #TotalAmount

SET RuningTotal = @amountSum, @amountSum = @amountSum + Cost

Now select both the tables to view changes. 

SELECT * FROM #amount

SELECT * FROM #TotalAmount

DROP TABLE #amount, #TotalAmount