如何按 Column1 分组并将 Column2 与 Count(*) 相乘？

发布于 2024-12-04 07:49:42 字数 680 浏览 0 评论 0原文

这就是我想要实现的目标：

SELECT  COUNT(*) * dd.Material_Quantity as [Count], tabSparePart.SparePartName
FROM         tabDataDetail AS dd INNER JOIN
                      tabSparePart ON dd.fiSparePart = tabSparePart.idSparePart
WHERE    dd.Reused_Indicator = 1
GROUP BY tabSparePart.SparePartName
ORDER BY Count DESC, tabSparePart.SparePartName

计算所有备件的出现次数并将它们与其相应的Material_Quantity值相乘。但我收到以下错误：

8118：列“tabDataDetail.Material_Quantity”在选择列表中无效，因为它未包含在聚合函数中并且没有 GROUP BY 条款。

我怎样才能有意义地将两个值相乘？简单的 SUM 或 AVG 会给出正确的结果吗？例如：COUNT(*) * AVG(dd.Material_Quantity) as [Count]

原文

this is what i'm trying to achieve:

SELECT  COUNT(*) * dd.Material_Quantity as [Count], tabSparePart.SparePartName
FROM         tabDataDetail AS dd INNER JOIN
                      tabSparePart ON dd.fiSparePart = tabSparePart.idSparePart
WHERE    dd.Reused_Indicator = 1
GROUP BY tabSparePart.SparePartName
ORDER BY Count DESC, tabSparePart.SparePartName

Count all sparepart occurences and multiply them with their corresponding Material_Quantity value.
But i get following error:

8118: Column 'tabDataDetail.Material_Quantity' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY
clause.

How can i multiply both values meaningfully? Would a simple SUM or AVG give the correct result? For example: COUNT(*) * AVG(dd.Material_Quantity) as [Count]

分享到QQ

分享到微博