using COALESCE in mysql to get the shipping charges -

need create sql view calculating commission & shipping charges. shipping charges per item charged on order id level can have multiple order items please have @ image attached. pls note shipping charges @ order id level.and not @ order item id level. order item id column blank shipping charges row.so in order shipping charges need divide no of items in order

order item id no of items in order, each order id can have 1 or multiple order item id's image of view required

sql fiddle schema

select main.order_id, main.order_item_id, coalesce(p.a,0) + coalesce(c.a,0) - (coalesce(s.a,0) / main.c) price       (((select order_id, order_item_id, count(*) c main group order_id, order_item_id) main left outer join      (select amount main description='principal') p on main.order_id =p.order_id , main.order_item_id = p.order_item_id)  left outer join      (select amount main description='commission') c on main.order_id=c.order_id , main.order_item_id = c.order_item_id)  left outer join       (select amount main description='shipping charges') s on main.order_id=s.order_id , main.order_item_id = s.order_item_id 

above sql query gives error #1054 - unknown column 'p.order_id' in 'on clause' can me in correcting desired result in image attached.

try this

    select main.order_id, main.order_item_id, coalesce(p.a,0) + coalesce(c.a,0) - (coalesce(s.a,0) / main.c) price       (((select order_id, order_item_id, count(*) c main group order_id, order_item_id) main left outer join      (select main.order_id,main.order_item_id,amount main description='principal') p on main.order_id =p.order_id , main.order_item_id = p.order_item_id)  left outer join      (select main.order_id,main.order_item_id,amount main description='commission') c on main.order_id=c.order_id , main.order_item_id = c.order_item_id)  left outer join       (select main.order_id,main.order_item_id,amount main description='shipping charges') s on main.order_id=s.order_id , main.order_item_id = s.order_item_id