3

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

0

2 Answers 2

3

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.

3
  • Out of curiosity, why don't you group by designation?
    – mustaccio
    Apr 29 at 11:52
  • 1
    @mustaccio Good point, I was having a brain fart when I originally wrote that, so you're right that generally you could group by the value field here. The one case where not grouping by it makes sense though is if it's not unique, i.e. there are two different 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.
    – J.D.
    Apr 29 at 18:32
  • @mustaccio Also clearly my brain fart continues, as to solve the problem I mentioned, I can just keep id in the grouping key as well. Updated my answer lol. Thanks.
    – J.D.
    Apr 29 at 19:15
-1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.