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.

Tim Biegeleisen · Accepted Answer · 2017-01-16T15:38:41.360

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

edited Jan 16 '17 at 15:38

answered Jan 16 '17 at 14:58

Tim Biegeleisen

502,043
27
286
360

I need to execute my script using only RIGHT JOIN, can you please help me with that? – AGK Jan 16 '17 at 15:09
Hi Oliver, check my update, just swap the table order in the join and replace `LEFT` with `RIGHT`, that's it. – Tim Biegeleisen Jan 16 '17 at 15:10
Hi Tim. Sorry, I forgot that I'm only allowed to use RIGHT JOIN. I can't use Inner Join. :( – AGK Jan 16 '17 at 15:12
You can replace the `INNER JOIN` with a `RIGHT JOIN`, it should make no difference in this case, because every product code should match. – Tim Biegeleisen Jan 16 '17 at 15:14
Okay Thanks Tim. :) – AGK Jan 16 '17 at 15:15
Tim, t2."Total quantity" is not valid in the context where it is used, can you please help me. – AGK Jan 16 '17 at 15:27
@OliverSpicerPrudentino Just use `total_quantity` (one word) as an alias, and then refer to this in the outer query. – Tim Biegeleisen Jan 16 '17 at 15:41

GROUP BY AND RIGHT JOINS IN DB2

1 Answers1