Click here to Skip to main content
11,709,536 members (34,875 online)
Click here to Skip to main content

Applying Running Total to a Result set

, 8 May 2012 CPOL 3.5K 32
Rate this:
Please Sign up or sign in to vote.
This tip describes how to add a running total column to a result set


Let's have a quick look at how to add a running total column to a result set.


Here there are two tables called GRN and Invoice. GRN contains all the received products while Invoice contains issued products.

Let's insert the concatenated result set into a memory table. Here invoice Qty is inserted as (-) value. Remember to order the result by date before insert. Declare a variable to hold the running total and update running total column.

Using the Code

--- Declare a memory table
	ProductID	INT,
	Qty		INT,
	RuningTotal     INT	
---- Concatenate the result from GRN and Invoice table
---- Set invoice Qty as (-) value
---- Insert result set in the memory table ordered by date
INSERT INTO @ProductList
SELECT ProductID, [DATE], QTY, 0 AS RuningTotal 
(SELECT ProductID, [DATE], QTY, 0 AS RuningTotal FROM GRN 
SELECT ProductID, [DATE], - (QTY), 0 AS RuningTotal FROM Invoice 
---- Declare running balance variable
DECLARE @RunningBalance INT
SET @RunningBalance=0
---- Update the running total column
UPDATE @ProductList
SET  @RunningBalance = RuningTotal = @RunningBalance + (Qty)     
FROM @ProductList
---- Result
SELECT * FROM @ProductList

The result will appear as follows:


  • May 09, 2012: Article created


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Chamila Ranasinghe
Software Developer
Sri Lanka Sri Lanka
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionSingle query? Pin
Mika Wendelius9-May-12 7:46
mvpMika Wendelius9-May-12 7:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150819.1 | Last Updated 9 May 2012
Article Copyright 2012 by Chamila Ranasinghe
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid