表达式中不能有聚合函数

Question

您好我正在尝试做下面的qry，并且我不断收到subqry总和线上的“不能有表达式中的聚合函数”错误。任何方式使这项工作？

SELECT ID, 
    DateColumn, 
    Contamination, 
    BrokenGlass, 
    OtherReasons, 
    SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons) AS Total, 
    SUM(Contamination)/Total AS Expr1, 
    (SELECT SUM(Contamination)/ SUM(SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 
    FROM tbltest T2 
    WHERE T2.ID <= T2.ID) AS RunningSum 
FROM tbltest 
GROUP BY ID, DateColumn, Contamination, BrokenGlass, OtherReasons 

Answer 1

SELECT ID, 
    DateColumn, 
    Contamination, 
    BrokenGlass, 
    OtherReasons, 
    SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons) AS Total, 
    SUM(Contamination)/Total AS Expr1, 

    /* CHANGED THIS LINE */ 
    (SELECT SUM(Contamination)/ (SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 
    FROM tbltest T2 
    WHERE T2.ID <= T2.ID) AS RunningSum 

FROM tbltest 
GROUP BY ID, DateColumn, Contamination, BrokenGlass, OtherReasons 

编辑

结果是不正确是比原先要求一个完全不同的问题，我将不得不采取在黑暗中刺在这一点，因为我什么都不知道你的项目，但在这里，你可能会让你开始朝正确的方向发展。

SELECT ID, 
    DateColumn, 
    Contamination, 
    BrokenGlass, 
    OtherReasons, 
    SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons) AS Total, 
    SUM(Contamination)/Total AS Expr1, 
    (
     SELECT SUM(Contamination)/ (SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 
     FROM tbltest T2 
     WHERE T2.ID <= tbltest.ID 
    ) AS RunningSum 
FROM tbltest 
GROUP BY ID, DateColumn, Contamination, BrokenGlass, OtherReasons 

Answer 2

我猜你正在使用基于Expr1的MS-Access，请尝试以下操作。替换在查询中Total别名使用和你不能SUM(SUM())：

SELECT ID, 
    DateColumn, 
    Contamination, 
    BrokenGlass, 
    OtherReasons, 
    SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons) AS Total, 
    SUM(Contamination)/(SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) AS Expr1, 
    (SELECT SUM(Contamination)/ (SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 
    FROM tbltest T2 
    WHERE T2.ID <= tbltest.ID) AS RunningSum -- I think you want T2.ID <= tbltest.ID not T2.ID <= T2.ID 
FROM tbltest 
GROUP BY ID, DateColumn, Contamination, BrokenGlass, OtherReasons 

Answer 3

更改sum(sum())只使用所产生的款项加法运算符：

--SUM(SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 
(SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons)) 

此外，不要试图引用在同一个select子句中的别名。可以使用子查询/派生表，也可以重复“总计”所需的计算。

--SUM(Contamination)/Total AS Expr1 
SUM(Contamination)/(SUM(Contamination) + SUM(BrokenGlass) + SUM(OtherReasons))