create average cost for multiple item
inventory_item_id | transaction_id| primary_quantity| price
1351 | 1 | 1000 | 100
1351 | 2 | -500 | 110
1351 | 3 | 1500 | 80
1351 | 4 | -100 | 150
1351 | 5 | -600 | 110
1351 | 6 | 700 | 105
1351 | 7 | -700 | 110
1351 | 8 | 200 | 120
1352 | 9 | 250 | 18.64
1352 | 10 | 105 | 29.75
1352 | 11 | -2 | 35
output
inventory_item_id | transaction_id| primary_quantity| price | total_vol | total_costs | unit_costs
1351 | 1 | 1000 | 100 | 1000 | 100000 | 100
1351 | 2 | -500 | | 500 | 50000 | 100
1351 | 3 | 1500 | 80 | 2000 | 170000 | 85
1351 | 4 | -100 | | 1900 | 161500 | 85
1351 | 5 | -600 | | 1300 | 110500 | 85
1351 | 6 | 700 | 105 | 2000 | 184000 | 92
1351 | 7 | -2000 | | 0 | 110500 | 92
1351 | 8 | 700 | 120 | 700 | 184000 | 120
1352 | 9 | 250 |18.64 | 250 | 4660 | 18.64
1352 | 10 | 105 | 29.75| 355 | 7783.75 | 21.9
1352 | 11 | -2 | | 353 | 7739.89 | 21.9
inventory_item_id ,transaction_id 1351,1--> if primary_quantity positive then unit_cost = (primary_quantity *price)+(total_vol*last unit cost)/(primary_quantity +last_total_vol) 1000*100/100=100
total_costs = total_vol * unit_cost 1000*100 = 100000
inventory_item_id ,transaction_id 1351,2--> if primary_quantity negative then last unit cost 100
inventory_item_id ,transaction_id 1351,3--> if primary_quantity positive then unit_cost = (primary_quantity *price)+(total_vol*last unit cost)/(primary_quantity +last_total_vol)
(1500*80)+ (500*100)/(1500+500)=85
total_costs = total_vol *