mysql;迭代表并计算平均价格

Question

我从 podetail 表中得到以下查询，在该表中我一次计算“一个”唯一商品的平均价格。我需要创建一个“avgUnitCost”列表。有没有办法在子查询中使用这个现有查询来生成 avgUnitCost 列表？

SELECT  items.`itemName`,
        SUM(podetails.`qtyReceived`) AS qtyRcvd,
        SUM(podetails.`qtyReceived` * podetails.`unitCost`) AS totalUnitCost,
        SUM(podetails.`qtyReceived` * podetails.`unitCost`) / SUM(podetails.`qtyReceived`) AS avgUnitCost

FROM podetails, items, purchaseorders
WHERE purchaseorders.`poID` = podetails.`poID`
  AND podetails.`itemID` = items.`itemID`
  AND podetails.`itemID` = 3;>

Answer 1

此处缺少 GROUP BY 语句。不确定具体在哪一列，但假设您应该按 items.itemName 进行分组，例如

SELECT  items.`itemName`,
    SUM(podetails.`qtyReceived`) AS qtyRcvd,

    SUM(podetails.`qtyReceived` * podetails.`unitCost`) AS totalUnitCost,

    SUM(podetails.`qtyReceived` * podetails.`unitCost`) / SUM(podetails.`qtyReceived`) AS avgUnitCost

FROM podetails JOIN purchaseorders

ON
    purchaseorders.`poID` = podetails.`poID`
JOIN items

ON
    podetails.`itemID` = items.`itemID`

WHERE
    podetails.`itemID` = 3

GROUP BY items.`itemName`;