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
帮帮我!我的脚本未运行。我需要获取每种产品的总量,但是我的分组依据却无效。
最佳答案
在单独的子查询中计算每种产品的数量总和,然后将其返回到原始product
表:
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
请注意,通过切换子查询中联接表的顺序,我用
RIGHT JOIN
替换了LEFT JOIN
。更新:
如果您绝对需要使用
RIGHT JOIN
,则只需将子查询替换为:SELECT p.prodCode, SUM(sd.quantity) total_quantity
FROM salesDetail sd
RIGHT JOIN product p
ON p.prodCode = sd.prodCode
GROUP BY p.prodCode
关于group-by - DB2中的分组依据和正确联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41679077/