mysql - mysql;遍历表格并计算平均价格

我从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;>

最佳答案

您在这里缺少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`;

关于mysql - mysql;遍历表格并计算平均价格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39560299/