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

I have 2 tables

1. product

2)sub_product

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

Notes : I use SQL server

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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.

Method 2

SELECT Designation, SUM(Qty)
FROM Product P
JOIN sub_product Q
ON Q.Product_ID = P.id
GROUP BY Designation
ORDER BY Designation

? downvoted? LOL

DECLARE @Product TABLE (ID INT, Designation VARCHAR(10))
DECLARE @Sub_Product TABLE ( product_id INT, name varchar(10), Quantity INT)
INSERT INTO @Product (ID, Designation) VALUES
(1,'coca'),
(2,'Pepsi'),
(3,'Fanta'),
(4,'Seven'),
(5,'Eight')

INSERT INTO @Sub_Product (product_id, name, Quantity) VALUES
(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)

SELECT Designation, SUM(Quantity) [Total Quantity]
FROM @Product P
JOIN @sub_product Q
ON Q.Product_ID = P.id
GROUP BY Designation
ORDER BY Designation

RESULT:

Designation Total Quantity
coca    40
Fanta   30
Pepsi   50
Seven   50

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0