Post reply

sum 2 column by group

  • February 22, 2023 at 11:27 pm

    #4153051

    Hi,

    if OBJECT_ID ('tempdb..#tmp') is not null drop table #tmp;

    create table #tmp (TrId INT,SellPrice decimal(18,4),kode varchar(20),DiffAmt decimal(18,4),Rtotal decimal (18,4))

    insert into #tmp (TrId,SellPrice,kode,DiffAmt)

    select 1 ,27560.501 ,'3000276' , 0.50 union all

    select 2 ,27560.501 ,'3000276' , 0.50 union all

    select 3 ,27560.501 ,'3000449' , 0.30 union all

    select 4 ,27560.501 ,'3000449' , 0.50 union all

    select 5 ,27560.501 ,'3000449' , 0.40 union all

    select 6 ,27560.501 ,'3000449' , 0.50

    select * from #tmp

    i want to update/or select Rtoal with running total per Kode and Trid. how can I do so

    TrId SellPrice  kode        DiffAmt            Rtotal (sellprice+DiffAmt)

    1 27560.5010  3000276 0.5000     27,561.001 (27560.5010+.5)

    2 27560.5010  3000276 0.5000     27,561.501 (27,561.001+.5)

    3 27560.5010  3000449 0.3000     27,560.801 (27560.5010+0.3)

    4 27560.5010  3000449 0.5000     27,561.301 (27,560.801+0.5)

    5 27560.5010  3000449 0.4000    27,561.701 (27,561.301+0.4)

    6 27560.5010  3000449 0.5000     27,562.201 (27,561.701+0.5)

  • February 23, 2023 at 12:39 am

    #4153065

    Maybe I'm just not seeing it. Are you just trying to create a running total?

    You need a windowing function to do that...

    SELECT trID,
    DiffAmt,
    rt = SUM(DiffAmt) OVER (ORDER BY trID
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    FROM #tmp
    ORDER BY trID

  • February 23, 2023 at 1:06 am

    #4153066 

    SELECT TrId, SellPrice, kode, DiffAmt,
        SellPrice + SUM(DiffAmt) OVER (ORDER BY TrId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Rtotal
    FROM #tmp

  • February 23, 2023 at 7:57 am

    #4153158

    Yes,on group of TrId and Kode, like illustrated below

    TrId SellPrice         kode          DiffAmt     Rtotal (sellprice+DiffAmt)

    1        27560.5010  3000276    0.5000    27,561.001 (27560.5010+.5) (sellprice+DiffAmt where trid=1 and kode= 3000276)

    2       27560.5010  3000276     0.5000    27,561.501 (27,561.001+.5) (RTotal of  (trid=1 and kode= 3000276) + DiffAmt where trId=2 and Kode=3000276)

    3       27560.5010  3000449     0.3000     27,560.801 (27560.5010+0.3) (sellprice+DiffAmt where trid=3 and kode= 3000449     )

    4       27560.5010  3000449    0.5000      27,561.301 (27,560.801+0.5) (RTotal of  (trid=3 and kode= 3000449    ) + DiffAmt where trId=4 and Kode=3000449    )

     

    and so on

  • February 23, 2023 at 10:54 am

    #4153223

    Following worked

    SELECT TrId SellPrice kode DiffAmt,

    sellprice+sum(DiffAmt) OVER (PARTITION BY kode

    ORDER BY trId

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND 0 PRECEDING)

  • February 23, 2023 at 5:17 pm

    #4153352

    0 PRECEDING?

    isn't that CURRENT ROW?  Doesn't matter to me, but if you can write the query in a standard way, it's easier for people to understand what your code is doing.

    • This reply was modified 1 year, 9 months ago by  pietlinden.

  • February 23, 2023 at 5:19 pm

    #4153354

    Yes it worked :):)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply