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

The question:

I have 2 tables

  1. product
id designation
1 coca
2 Pepsi
3 Fanta
4 Seven
5 Eight


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

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:

    SUM(ISNULL(sp.quantity, 0)) AS [total quantity]
FROM product AS p
LEFT JOIN sub_product AS sp
    ON = sp.product_id
GROUP BY, 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 =
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

INSERT INTO @Sub_Product (product_id, name, Quantity) VALUES

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


Designation Total Quantity
coca    40
Fanta   30
Pepsi   50
Seven   50

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

Leave a Comment