SQL - Select customer's orders with by adding and distinct products

Question

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

Answer 1

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