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

[kalyn]

I have 2 tables

1. 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

[emerson]

This can be accomplished simply with a https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 and https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15 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.