.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!



Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

How to calculate Running total in Sql Server

Posted By: Deco     Posted Date: September 22, 2010    Points:2   Category :Sql Server
I need to calculate the running total, please help with your query

My Table

Customer ProductName Quantity ProductPrice Amount
------------------------------------------------------
Alwyn KeyBoard 1 100 100
Joe Durai Mouse 2 70 140
Joy Bryna Cooler Fan 1 60 60


My expected Output is


Customer ProductName Quantity ProductPrice Amount Running_Total
--------------------------------------------------------------------
Alwyn KeyBoard 1 100 100 100
Joe Durai Mouse 2 70 140 240
Joy Bryna Cooler Fan 1 60 60 300


Responses
Author: Jean Paul             
Posted Date: September 27, 2010     Points: 5   

There are multiple ways to achieve this - I am listing two of them

Way 1) Using Temporary tables in sql server
create table #calc (customer varchar(MAX), productname varchar(MAX), quantity int, productprice int, amount
int, runningtotal int);

DECLARE @customer varchar(MAX),
@productname varchar(MAX),
@quantity int,
@productprice int,
@amount int,
@runningtotal int;

DECLARE cursor1 cursor for select customer, productname, quantity, productprice, amount from mytable;

OPEN cursor1;

FETCH NEXT FROM cursor1 INTO @customer, @productname, @quantity, @productprice, @amount
SET @runningtotal = 0;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @runningtotal = @runningtotal + @amount;

INSERT INTO #calc VALUES (@customer, @productname, @quantity, @productprice, @amount, @runningtotal);
FETCH NEXT FROM cursor1 INTO @customer, @productname, @quantity, @productprice, @amount
END

close cursor1
deallocate cursor1

select * from #calc;

drop table #calc;


Way 2) Using sum(Amount) upto the current record - but as we do not have record id this is not possible
sql: select id, amount, (select sum(amount) from table1 where id <= t.id) from table1 t

Jean Paul http://jeanpaulva.com/


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend