CODEWITHSUNDEEP
sqlms-access
user1342164
user1342164

Reputation: 1454

Cannot have aggregate function in expression

Hi I am trying to do the qry below and I keep getting "Cannot have aggregate function in expression" error on the subqry sum line. Any way to make this work?

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

Upvotes: 1

Views: 6511

Answers (3)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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

Edited

The results being incorrect is a whole different problem than what was originally asked and I'm going to have to take a stab in the dark at this because I know nothing about your project, but here you go and maybe it can get you started in the right direction.

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

Upvotes: 1

Taryn
Taryn

Reputation: 247630

I am guessing that you are using MS-Access based on the Expr1, try the following. Replace the Total alias usage in the query and you cannot 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

Upvotes: 3

Robert
Robert

Reputation: 8767

Change your sum(sum()) to just use the addition operator for the resulting sums:

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

In addition, don't attempt to reference an alias in the same select clause. Either use a sub-query/derived table or just repeat the desired calculation for 'Total'.

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

Upvotes: 2

Related Questions