I have a situation where i want running sum like at the time of insert..
like when i insert some record in the table then there is a column in a table which i want to update at the time of insertion like.. based on ID column
ID Value SUM
1 100 100
1 -50 50
2 150 150
1 100 150
As mentioned above At first i Insert ID=1 and value =100 then sum is 100
again i insert id=1 and value=-50 then the sum column be like 50
again i insert id=2 and value=150 so count is 150
and again i insert id=1 and value=100 so 50+100 is 150 that is count.
So can anybody tell em please how to achieve this.
Peter9: Hi Vishak yes i have a primary key column in my table @madhi i like to calculate at run time like i shoot Insert statement and if i check select * from table then i like to have results.
Hi Vishak yes i have a primary key column in my table
@madhi i like to calculate at run time like i shoot Insert statement and if i check select * from table then i like to have results.
If you have primary column, all you need to do is
select t1.id, (select sum(value) from your_table where id=t1.id and primary_col<=t1.primary_col) from your_table as t1
Replace your_table and primary_col with respective names
Madhivananhttp://beyondrelational.com/blogs/madhivanan
@Peter9 and @Madhivanan,
Well, the solution provided by Madhivanan is the best solution if multiple records are going to be inserted in table (like... insert into @t select col). But if one one record is inserted then we should better use the max keyword to find the last value of SUM column to add it with new value.
hey still no help.. please guys this is urgent one??
Anybody here to help??
Peter9: hey still no help.. please guys this is urgent one?? Anybody here to help??
declare @t table(ID int, Value int) insert into @t select 1, 100 union all select 1 , -50 union all select 2 ,150 union all select 1 ,100 declare @t1 table(id int, value int, sno int) insert into @t1 select id,value,row_number() over (order by id) as sno from @t select t1.id, (select sum(value) from @t1 where id=t1.id and sno<=t1.sno) from @t1 as t1
Hi Peter,
Do you have a unique valued column in your table ( may be Primary Key ID column or audit column like datecreated)? Also are you using SQL 2005?