sqlsql-server-2017

need distribute group value based on weightage avg

my table data structure like that.

SNo Name Color Ref no OrdQty Act Qty
1 Ankur Red 1.1 80 120
2 Ankur Red 1.2 30 120
3 Neeraj Black 1.1 50 110
4 Neeraj Black 1.2 50 110
5 Neeraj Red 1.3 30 28

I need to distribute "ACT Qty" based on weightage avg. weightag avg calc. based on "ordqty" in group by Name , color. with new column weightage. result as below

SNo Name Color Ref no OrdQty Act Qty Wightage
1 Ankur Red 1.1 80 120 87
2 Ankur Red 1.2 30 120 33
3 Neeraj Black 1.1 50 110 55
4 Neeraj Black 1.2 50 110 55
5 Neeraj Red 1.3 30 28 28

Formula

S Name COl Ref ordqty Actqty Formula cal Result
1 Ankur Red 1.1 80 120 (80/110)*120 87
2 Ankur Red 1.2 30 120 (30/110)*120 33 
formulacal= (ordqty/sum(orderqty))*actqty=87 
            (ordqty/sum(orderqty))*actqty=33
Solution

  • you can use window function to obtain the sum(ordqty) of a group.

    Wightage = OrdQty * 1.0 
         / sum(OrdQty) over (partition by Name, Color) 
         * ActQty

    multiplication by 1.0 is to avoid integer division

    demo