SQL - Select customer's orders with by adding and distinct products [closed]-CodePudding

I have 3 tables :

CUSTOMERS :

CUSTOMER_ID | CUSTOMER_NAME

ORDERS :

ORDER_ID | CUSTOMER_ID | PRODUCT_ID | QUANTITY | PRICE

PRODUCTS :

PRODUCT_ID | NAME | PRICE

I would like to list all products ordered by a customer but with adding all quantities.

Here is the result I would like to have :

CUSTOMER ID | PRODUCT_ID(1) | QUANTITIES (SUM)

CUSTOMER ID | PRODUCT_ID(2) | QUANTITIES (SUM)

CUSTOMER ID | PRODUCT_ID(3) | QUANTITIES (SUM)

CUSTOMER ID | PRODUCT_ID(4) | QUANTITIES (SUM)

Here is my actual request which only list all orders with all the informations (without SUM) :

SELECT CUS.*, ORD.*, PRO.*

FROM `customers` CUS

LEFT JOIN `orders` ORD
ON ORD.customer_id = CUS.customer_id

LEFT JOIN `products` PRO
ON PRO.product_id = ORD.product_id 

WHERE CUS.customer_id = 4697

Thank you

CodePudding user response：

You need to use a sum and a group by, only selecting the columns you want would make it easier too.

select customer_id, product_id, sum(quantity)
FROM `customers` CUS

LEFT JOIN `orders` ORD
ON ORD.customer_id = CUS.customer_id

LEFT JOIN `products` PRO
ON PRO.product_id = ORD.product_id 

WHERE CUS.customer_id = 4697
group by customer_id, product_id

CodePudding user response：

SELECT
    PRO.product_name,
    ROUND(SUM(ORD.price),2),
    ROUND(SUM(ORD.quantity), 2)

FROM ORDERS ORD

LEFT JOIN PRODUCTS PRO
ON PRO.product_id = ORD.product_id

WHERE ORD.customer_id = 4697

GROUP BY ORD.product_id

Thank you ;)