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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply