locked
accumulating two cells RRS feed

  • Question

  • Hi,
    Suppose we have a table as bellow:

    FirstColumn         LastColumn          SomeNumber
    ----------------------------------------------------------
          A                         B                         10
          C                         D                         20
          E                          F                         30
          G                         H                         40

    How can I have a SELECT statement that produce a result as bellow:

    FirstColumn         LastColumn            NewValue
    ----------------------------------------------------------
          A                         B                         10
          C                         D                         30
          E                          F                         60
          G                         H                        100

    Note: The NewValue column is sum of current row and previous row.
    MJ_Developer.
    Sunday, March 7, 2010 8:05 PM

Answers

All replies

  • Try

    select DATEADD(second, 1134410619, '19700101')
    
    declare @t table (id int identity primary key, FirstColumn varchar(10),        LastColumn varchar(10),  SomeNumber int, NewValue int null)
    
    insert into @t (FirstColumn, LastColumn, SomeNumber) select 'A',  'B',                         10
    union all select
          'C',                         'D',                         20
    union all select      
          'E',                          'F',                         30
    union all select      
          'G'                         ,'H',                         40
    
    
    declare @NewVal int
    set @NewVal = 0
    
    update @t set @NewVal = NewValue = @NewVal + SomeNumber
    
    select * from @t      
    

    See Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Chirag Shah Sunday, March 7, 2010 8:20 PM
    • Unproposed as answer by HunchbackMVP Sunday, March 7, 2010 8:36 PM
    Sunday, March 7, 2010 8:16 PM
  • Calculate running total, search this forum you would find plenty of answers

    http://www.sqlteam.com/article/calculating-running-totals
    Sunday, March 7, 2010 8:17 PM
  • No, I want to do sum at the SELECT statement and no use of UPDATE statement.

    MJ_Developer.
    Sunday, March 7, 2010 8:22 PM
  • Read the thread in question and references.

    select T.ID, T.FirstColumn, T.LastColumn, T.SomeNumber, 
    coalesce(F.Value,0) + T.SomeNumber as NewValue from @T T 
    CROSS APPLY (select SUM(SomeNumber) as Value from @t T1 where T1.id < T.ID) F

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, March 7, 2010 8:26 PM
  • First, you need to provide the logic that we can use to sort the rows, and allow us to identify each row chronologically.

    Let us suppose that you have an [ID] column that is incremental. Then we could use something like:

    select
        FirstColumn,
        LastColumn,
        SomeNumber,
        (
        select sum(B.SomeNumber)
        from T
        where B.ID <= A.ID
        ) as running_total
    from
        T as A;
    GO

    You could also use a cursor, which performs better for this kind of calculation.

    Here is a good article about calculating running aggregates in T-SQL.

    Subqueries and Joins for Running Aggregates
    http://scripting.windowsitpro.com/Windows/Articles/ArticleID/102236/pg/1/1.html


    AMB

    • Proposed as answer by Naomi N Tuesday, November 9, 2010 8:34 PM
    Sunday, March 7, 2010 8:35 PM
  • Naom,

    I unproposed as answer your post that is using "ordered update". This method is not documented and Microsoft does not guarantee that the update will be done in an ordered fashion.


    AMB
    Sunday, March 7, 2010 8:39 PM
  • Hey Alejandro,

    What is wrong with my original solution (of course, I read the referenced articles and in particular article by Jeff Moden) many times.

    See also http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&st=0&sk=t&sd=a&hilit=running+total (this long thread).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, March 7, 2010 8:42 PM
  • Hey Alejandro,

    What is wrong with my original solution (of course, I read the referenced articles and in particular article by Jeff Moden) many times.

    See also http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&st=0&sk=t&sd=a&hilit=running+total (this long thread).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Naom,

    Again, Microsoft does not guarantee that an update will be done in an ordered fashion, which is what you are taking for granted in that solution.

    Ordered UPDATE and Set-Based Solutions to Running Aggregates
    http://www.sqlmag.com/article/sql-server/ordered-update-and-set-based-solutions-to-running-aggregates.aspx


    AMB
    Sunday, March 7, 2010 9:04 PM
  • Hey Alejandro,

    What is wrong with my original solution (of course, I read the referenced articles and in particular article by Jeff Moden) many times.

    See also http://forum.lessthandot.com/viewtopic.php?f=17&t=7601&st=0&sk=t&sd=a&hilit=running+total (this long thread).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Naom,

    Again, Microsoft does not guarantee that an update will be done in an ordered fashion, which is what you are taking for granted in that solution.

    Ordered UPDATE and Set-Based Solutions to Running Aggregates
    http://www.sqlmag.com/article/sql-server/ordered-update-and-set-based-solutions-to-running-aggregates.aspx


    AMB

    Heh... but even you can't get one to break after it's been properly setup.
    --Jeff Moden
    Friday, May 7, 2010 4:42 AM