GROUP BY AND RIGHT JOINS IN DB2

Question

Select p.prodCode,
       p.description,
       p.unit,
       SUM(sd.quantity) "Total quantity"
FROM salesDetail sd
RIGHT JOIN product p
    ON p.prodCode = sd.prodCode
GROUP BY p.prodCode
ORDER BY 4 DESC

Help! My Script is not running. I need to get the total quantity of every product but my group by is not working.

Answer 1

Compute the sum of the quantity per product in separate subquery, and then join this back to the original product table:

SELECT t1.prodCode,
       t1.description,
       t1.unit,
       t2.total_quantity
FROM product t1
INNER JOIN
(
    SELECT p.prodCode, SUM(sd.quantity) total_quantity
    FROM product p
    LEFT JOIN salesDetail sd
        ON p.prodCode = sd.prodCode
    GROUP BY p.prodCode
) t2
    ON t1.prodCode = t2.prodCode

Note that I replaced the RIGHT JOIN with a LEFT JOIN by switching the order of the joined tables in the subquery.

Update:

If you absolutely need to use a RIGHT JOIN, then just replace the subquery with this:

SELECT p.prodCode, SUM(sd.quantity) total_quantity
FROM salesDetail sd
RIGHT JOIN product p
    ON p.prodCode = sd.prodCode
GROUP BY p.prodCode