locked
sum field with max record RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    Field1 Field2
    L-265 8.35
    L-265/1 3.96
    L-265/12 3.44
    KO-265 17.83
    MMMG-265 6.72

    I want resultset like this what ever / before value should add in / field1 value ex:- L-265  we elimate and take that value and add in L-265/1 and L-265/12

    Field1 Field2
    L-265/1 12.31
    L-265/12 11.79
    KO-265 17.83
    MMMG-265 6.72

    Thank You

    Thursday, June 11, 2020 1:41 PM

Answers

  • User452040443 posted

    Hi,

    Try:

    select coalesce(c.Field1, t.Field1) as Field1, sum(t.Field2) as Field2
    from MyTable as t
    outer apply
    (
        select l.Field1
        from MyTable as l
        where l.Field1 like t.Field1 + '/%'
    ) as c
    group by coalesce(c.Field1, t.Field1)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2020 3:22 PM