I have 2 tables
| 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.
products with the same designation but different ids (assuming the id field is unique here - either by primary key or some other constraint). Then the query I provided will ensure you get the accurate totals back with a separate row for each instance of the duplicate designation.
id in the grouping key as well. Updated my answer lol. Thanks.
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
