How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables

• product

id
designation

1
coca

2
Pepsi

3
Fanta

4
Seven

5
Eight

2)sub_product

product_id
name
quantity

1
sm
10

1
lg
10

1
xl
20

2
1L
10

2
2L
20

2
5L
20

3
Ty
10

3
Sa
20

4
ha
20

4
kd
30

what I wanna have is this:
the designation from the product table and total quantity which represent the SUM of the quantity that have the same product_id

designation
total quantity

Coca
40

Pepsi
50

Fanta
30

Seven
50

Notes : I use SQL server

This can be accomplished simply with a JOIN and GROUP_BY clause like so:

SELECT 
    p.designation, 
    SUM(ISNULL(sp.quantity, 0)) AS [total quantity]
FROM product AS p
LEFT JOIN sub_product AS sp
    ON p.id = sp.product_id
GROUP BY p.id, p.designation

Note the use of a LEFT OUTER JOIN (aka a LEFT JOIN) to ensure that even products without any sub_products are still returned (with a total quantity of 0). If it's guaranteed there'll always be at least 1 sub_product for every product then you can use an INNER JOIN instead.

StackExchange Database Administrators Q#311396, answer score: 5