locked
SQL Server sum field from previous calculation RRS feed

  • Question

  • User-1575600908 posted

    In SQL Server, I have table with 4 column

    artid    num     A           B
       46     1     417636000     0 
       47     1     15024000      0
      102     1     3418105650    0
      226     1     1160601286    0
      60     668    260000        0
      69     668    5500000       0

    I want in result set create new column for some calculation

    This column should have value like this:

    artid       num         a                      b                      newColumnValue
    ----------- ----------- ---------------------- ---------------------- ----------------------
    46          1           417636000              0                      a-b+previous newColumnValue

    I write this query, but I can't get previous newColumnValue:

    select *, (a- b+ lag(a- b, 1, a- b) over (order by num,artid)) as newColumnValue
     FROM MainTbl
      ORDER BY  num,artid

    i get this result

    artid       num         a                      b                      newColumnValue
    ----------- ----------- ---------------------- ---------------------- ----------------------
    46          1           417636000              0                      417636000
    47          1           15024000               0                      432660000
    102         1           3418105650             0                      3433129650
    226         1           1160601286             0                      4578706936
    60          668         260000                 0                      1160861286
    69          668         5500000                0                      5760000

    i want get this result

    artid       num         a                      b                      newColumnValue
    ----------- ----------- ---------------------- ---------------------- ----------------------
    46          1           417636000              0                      417636000
    47          1           15024000               0                      432660000
    102         1           3418105650             0                      3850765650
    226         1           1160601286             0                      5011366936
    60          668         260000                 0                      5011626936
    69          668         5500000                0                      5017126936

    can you help me?

    Saturday, December 9, 2017 2:56 PM

Answers

  • User991499041 posted

    Hi Sadeq.hatami,

    Use sum(a-b), not (a- b+ lag(a- b, 1, a- b)

    declare @tb table(artid int,num int,A decimal,B decimal)
    
    insert into @tb values
    (46,1,417636000,0),
    (47,1,15024000,0),
    (102,1,3418105650 ,0),
    (226,1,1160601286,0),
    (60,668,260000,0),
    (69,668,5500000,0)
    
    select *,sum(A-B) Over(Order by num,artid) as newColumnValue
    from @tb

    Screenshot

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 13, 2017 7:31 AM
  • User991499041 posted

    Hi Sadeq.hatami,

    thank's for your answer, can you explain me

    sum(A-B) Over(Order by num,artid)

    what exactly does it do? i ca'nt understand it

    Add an order by to the Over() and it will do a cumulative sum of the previous rows. 

    It means newColumnValue column is a cumulative sum of the (A-B) for the previous demand orders.

    Cumulative sum of previous rows

    http://www.sqlservercentral.com/articles/cumulative/109158/

    Calculate cumulative sum of previous rows in SQL Server

    http://sqlindia.com/calculate-cumulative-sum-of-previous-rows-sql-server/

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 13, 2017 8:38 AM

All replies

  • User-62323503 posted

    You can use Lead/Lag functions to access data from previous/next rows. 

    Refer below post

    http://www.itdeveloperzone.com/2012/04/lead-and-lag-functions-in-sql-server.html

    Sunday, December 10, 2017 9:26 AM
  • User991499041 posted

    Hi Sadeq.hatami,

    Use sum(a-b), not (a- b+ lag(a- b, 1, a- b)

    declare @tb table(artid int,num int,A decimal,B decimal)
    
    insert into @tb values
    (46,1,417636000,0),
    (47,1,15024000,0),
    (102,1,3418105650 ,0),
    (226,1,1160601286,0),
    (60,668,260000,0),
    (69,668,5500000,0)
    
    select *,sum(A-B) Over(Order by num,artid) as newColumnValue
    from @tb

    Screenshot

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 13, 2017 7:31 AM
  • User-1575600908 posted

    thank's for your answer, can you explain me

    sum(A-B) Over(Order by num,artid)

    what exactly does it do? i ca'nt understand it

    thank you

    Wednesday, December 13, 2017 7:52 AM
  • User991499041 posted

    Hi Sadeq.hatami,

    thank's for your answer, can you explain me

    sum(A-B) Over(Order by num,artid)

    what exactly does it do? i ca'nt understand it

    Add an order by to the Over() and it will do a cumulative sum of the previous rows. 

    It means newColumnValue column is a cumulative sum of the (A-B) for the previous demand orders.

    Cumulative sum of previous rows

    http://www.sqlservercentral.com/articles/cumulative/109158/

    Calculate cumulative sum of previous rows in SQL Server

    http://sqlindia.com/calculate-cumulative-sum-of-previous-rows-sql-server/

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 13, 2017 8:38 AM