locked
Cumulative Sum RRS feed

  • Question

  • User29410129 posted

    Actually scenario is i have a table name Account. And account table contain three columns (ID, Amount, GrandAmount). GrandAmount column contain cumulative amount.  i am just inserting amount in insert query.

    this is what i am looking.

    ID

    Amount

    GrandAmount

    1

    100

    100

    2

    500

    600

    3

    400

    1000

    waiting for your reply

    Monday, May 16, 2016 9:08 AM

Answers

  • User-219423983 posted

    Hi DanyalHaider,

     i am just inserting amount in insert query.

    According to your latest reply in your previous thread, I think you could use trigger to achieve your need. The following code you could have a look.

    create table MyTest0518(ID INT identity(1,1), Amout int, GrandAmout int)
    go
    create trigger TRG_Insert
    on MyTest0518
    after insert
    as
    begin
    	
    	declare @minid int, @maxid int
    	select @maxid = max(ID) from inserted
    	select @minid = min(id) from inserted
    	while @minid <= @maxid
    	begin
    		declare @preGrandAmout int
    
    		select @preGrandAmout = SUM(Amout) from MyTest0518 where ID <= @minid 
    
    		update MyTest0518 
    		set 
    			GrandAmout = @preGrandAmout
    		where ID = @minid 
    
    		set @minid = @minid+1
    	end
    
    end
    go
    insert into MyTest0518 (Amout) values (100),(500),(400)
    select * from MyTest0518

    Best Regards, <!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    Weibo Zhang<o:p></o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 10:55 AM

All replies

  • User-1636183269 posted

    Similar request : http://forums.asp.net/p/1827922/5083176.aspx

    Monday, May 16, 2016 9:19 AM
  • User77042963 posted

    For newer version of SQL Server, it is very easy to do it with SUM  Over... Order by ...

    Select ID, Amount, Sum(Amount) Over(Order by ID)  as GrandAmount from yourtable

    Monday, May 16, 2016 1:32 PM
  • User-698989805 posted

    Hello DanyalHaider! Please do the following:

    SELECT t.ID, t.Amount, (SELECT SUM(t2.Amount) FROM account t2
    WHERE t2.ID <= t.ID) AS TotalAmount FROM account t

    Tuesday, May 17, 2016 11:53 AM
  • User-219423983 posted

    Hi DanyalHaider,

     i am just inserting amount in insert query.

    According to your latest reply in your previous thread, I think you could use trigger to achieve your need. The following code you could have a look.

    create table MyTest0518(ID INT identity(1,1), Amout int, GrandAmout int)
    go
    create trigger TRG_Insert
    on MyTest0518
    after insert
    as
    begin
    	
    	declare @minid int, @maxid int
    	select @maxid = max(ID) from inserted
    	select @minid = min(id) from inserted
    	while @minid <= @maxid
    	begin
    		declare @preGrandAmout int
    
    		select @preGrandAmout = SUM(Amout) from MyTest0518 where ID <= @minid 
    
    		update MyTest0518 
    		set 
    			GrandAmout = @preGrandAmout
    		where ID = @minid 
    
    		set @minid = @minid+1
    	end
    
    end
    go
    insert into MyTest0518 (Amout) values (100),(500),(400)
    select * from MyTest0518

    Best Regards, <!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    Weibo Zhang<o:p></o:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 10:55 AM