SELECT id_product, SUM(COALESCE(received_qty, ordered_qty)) AS total
FROM (Select id_department,id_product,received_qty, ordered_qty from
(SELECT * FROM cart_item WHERE id_cart IN (SELECT id_cart FROM purchase)) AS c
LEFT JOIN Purchase p on p.id_cart = c.id_cart ) as Z
GROUP BY id_product
I am trying to get the total sum of qty from ordered_qty and received_qty, but it only sum the id_cart = 1 and ignored other id_cart.
What's wrong with my query? Thanks.
expected output : show total of quantity based on id_product from different purchase.