Cannot perform an aggregate function on an expression containing an aggregate or a subquery. I get t-CodePudding

 SELECT
     SUM(CEILING(COUNT(m1.ReportId) / @limitvalue))  
 FROM
     mytable2 m2
 INNER JOIN
     mytable1 m1 ON m1.ReportId = m2.Id 
 WHERE 
     m1.Createdby = 3 
     AND m1.ReportId = 1115

Assume @limitvalue is 3.

Thank you in advance for the help provided

CodePudding user response：

You have to use GROUP BY statement

CodePudding user response：

You can't use two aggregation functions (i.e. Sum and Count) in same selecte statement, you can split the query into 2, and Sum the output of the Count subquery

SELECT 
    SUM(sub.res)
FROM
    (SELECT 
        CEILING(COUNT(m1.ReportId) / @limitvalue) as res
    FROM
        mytable2 m2
    INNER JOIN mytable1 m1 ON m1.ReportId = m2.Id
    WHERE
        m1.Createdby = 3 AND m1.ReportId = 1115) AS sub;