Results 1 to 5 of 5

Thread: runningsum

  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unanswered: runningsum

    Hi,

    I have this problem. I need make a sql statment (not a SP) for this:

    Example:

    date amount
    1/1 10
    1/2 5
    1/3 20


    Now, my output is:

    date amount
    1/1 10
    1/2 15 (5+10)
    1/3 35 (15+15)

    any idea? in access can make this with a runningsum, but, in SQL 2000?

    TIA
    Abel.

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    I found a solution, with a subqueris, example
    select date, amount, O.amount
    ,(select sum(amount) from <myTable>
    where date <= O.date)
    'runningSum'
    from <myTable> O


    another idea?
    thanks.
    Abel.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Alternate method:
    Code:
    select	TBL1.date,
    	sum(TBL2.Amount) as RunningSum
    from	[MyTable] TBL1
    	inner join [MyTable] TBL2 on TBL1.date >= TBL2.date
    group by TBL1.date
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there are a couple of different sources that say a cursor (horrors!) is actually more efficient that set-based in the case of running totals:
    http://www.sqlteam.com/article/calcu...running-totals
    http://sqljunkies.com/WebLog/amachan.../28/18286.aspx

    but I guess the fastest way is to do it in a CLR proc:
    http://sqljunkies.com/WebLog/amachan.../28/18309.aspx

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Thanks blindman and jezmine.

    jezmine i read this link and tell to you. thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •