sql - 无法使用 group by 获得正确的 SUM

Question

我一直在尝试获取TotalCharge、TotalCost 和预计总小时数。

虽然我的脚本中没有错误，但我无法获得上述列的总数。

我的脚本如下:

SELECT
  l.LaborName,
  l.EstimatedHours,
  l.TotalCost,
  l.TotalCharge,
  w.CurrencySymbol,
  SUM(l.TotalCost) AS totalCost,
  SUM(l.TotalCharge) AS totalCharge,
  SUM(l.EstimatedHours) AS totalHours
FROM WOLabor l
INNER JOIN WO w
  ON l.WOPK = w.WOPK
WHERE l.WOPK = 10100
AND RecordType = 1
GROUP BY l.LaborName,
         l.EstimatedHours,
         l.TotalCost,
         l.TotalCharge,
         w.CurrencySymbol

这是我的输出:

我的预期输出是

Answer 1

您正在按只需要求和的列进行分组。 从GROUP BY中删除它们:

 SELECT
      l.LaborName,
      w.CurrencySymbol,
      SUM(l.TotalCost) AS totalCost,
      SUM(l.TotalCharge) AS totalCharge,
      SUM(l.EstimatedHours) AS totalHours
    FROM WOLabor l
    INNER JOIN WO w
      ON l.WOPK = w.WOPK
    WHERE l.WOPK = 10100
    AND RecordType = 1
    GROUP BY l.LaborName,
             w.CurrencySymbol