一个聚合不能出现在WHERE子句中，除非它出现在HAVING子句或select列表中的子查询中

Question

我正在运行SSMS 2008

select 
    A.JOBNUMBR as [Service Call], A.PONUMBER as [PO Number], 
    sum(A.QTYUNCMTBASE*A.UNITCOST) as [Committed Cost PO], 
    SUM(B.WS_Committed_Cost) as [Committed Cost WS], 
    (sum(A.QTYUNCMTBASE*A.UNITCOST)-SUM(B.WS_Committed_Cost)) as [Variance]
from 
    POP10110 A
LEFT JOIN 
    SV_Costs B on A.JOBNUMBR = B.Service_Call_ID and A.PONUMBER = B.Reference_TRX_Number and B.WS_Committed_Cost <> 0
where 
    A.Product_Indicator = 3
    and A.QTYUNCMTBASE <> 0
    and (sum(A.QTYUNCMTBASE * A.UNITCOST) - SUM(B.WS_Committed_Cost)) <> 0
group by 
    A.JOBNUMBR, A.PONUMBER
order by 
    A.JOBNUMBR, A.PONUMBER

当我运行这个查询时，我在主题行中得到MSG 147错误。第三个where子句与聚合列[方差]相同，我试图过滤到<> 0。查询运行时没有第三个where子句。

我一直在做一些阅读和理解，我需要在where子句中包括HAVING或select语句，但我不能完全得到语法正确，任何帮助将不胜感激。

谢谢

塔尼亚

Answer 1

Sum是聚合列，不能用于选择(WHERE子句)。一种解决方案是将其封装在另一个SELECT上。或:

SELECT
          A.JOBNUMBR as [Service Call], 
          A.PONUMBER as [PO Number], 
          SUM(A.QTYUNCMTBASE*A.UNITCOST) as [Committed Cost PO], 
          SUM(B.WS_Committed_Cost) as [Committed Cost WS], 
          SUM(A.QTYUNCMTBASE*A.UNITCOST) - SUM(B.WS_Committed_Cost) as [Variance]
FROM      POP10110 A
LEFT JOIN SV_Costs B 
       ON A.JOBNUMBR = B.Service_Call_ID 
          AND A.PONUMBER = B.Reference_TRX_Number 
          AND B.WS_Committed_Cost <> 0
WHERE     A.Product_Indicator = 3
      AND A.QTYUNCMTBASE <> 0
GROUP BY  A.JOBNUMBR, A.PONUMBER 
   HAVING (SUM(A.QTYUNCMTBASE * A.UNITCOST) - SUM(B.WS_Committed_Cost)) <> 0
ORDER BY  A.JOBNUMBR, A.PONUMBER