MySQL Forums
Forum List  »  Newbie

Need help on query which show total sum of result incorrect
Posted by: zell dsf
Date: November 28, 2017 08:09PM

SELECT id_product,sum((COALESCE(received_qty, quantity)))
FROM (SELECT C.id_cart,C.received_qty,C.quantity , P.id_product,
PO.id_purchase_order, PO.status
FROM (SELECT * FROM cart_items WHERE id_cart IN (SELECT id_cart FROM purchase_orders)) AS C
LEFT JOIN products as P on p.id_product = c.id_product
LEFT JOIN purchase_orders AS PO ON C.id_cart = PO.id_cart ) AS A
GROUP By A.id_product

Process:

When user buy item and check out then there's cart and cart items table to store the transaction.
1 cart_id have many item which stored in cart items table.
After purchase succeed, then will generate a purchase order id and stored in purchase order table .
In purchase order table, id_cart and status will be stored.

From here, i am trying to calculate quantity based on id_product or id branch or etc from the purchase made. There is receiving and ordered quantity field, which in some cases quantity received field might be null, so i will take ordered quantity value.

The expected result should sum up the qty of product id from different cart and display them.

Options: ReplyQuote


Subject
Written By
Posted
Need help on query which show total sum of result incorrect
November 28, 2017 08:09PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.