All we need is an easy explanation of the problem, so here it is.
I have 2 tables
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
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.
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.
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
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 🙂