Running Total With T-SQL


There are a few ways to calculate running total from a table. You can find samples from this link: http://www.sqlteam.com/article/calculating-running-totals.
I have used the Ken Anderson’s Guru solution in below example:

 
DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price DECIMAL)

INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 500),(2, 'B', 200),(3, 'C', 100),(4, 'E', 300)


SELECT a.ProductID,  a.Price 
FROM @Products a,  @Products b
WHERE b.ProductID <= a.ProductID 
GROUP BY  a.ProductID, a.Price
Having SUM(b.Price)<1000
ORDER BY  ProductID, Price

 

In SQL Server 2012 the enhanced window function with SUM Over(Order By ) provides an easy solution:

 
DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price DECIMAL)

INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 500),(2, 'B', 200),(3, 'C', 100),(4, 'E', 300)


--SELECT a.ProductID,  a.Price 
--FROM @Products a,  @Products b
--WHERE b.ProductID <= a.ProductID 
--GROUP BY  a.ProductID, a.Price
--Having SUM(b.Price)<1000
--ORDER BY  ProductID, Price



--SQL Server 2012
SELECT ProductID,  Price FROM (SELECT ProductID,  Price 
, SUM(Price) Over(Order By ProductID) as runningTotal
FROM @Products ) t
WHERE runningTotal<1000
ORDER BY  ProductID

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s