How can I perform an aggregate function in an expression containing an aggregate or subquery?

Question

I have a request like this

SELECT Id ,sum(CASE WHEN ErrorId NOT IN ( ,10 ,11 ,12 ,13 ) THEN 1 ELSE 0 END) errorCount FROM Table group by Id 

I don’t like the hard list of identifiers and I have a simple request that will get me what I want

 SELECT Id ,sum(CASE WHEN ErrorId NOT IN ( select ErrorId from Errors where ErrorCategory = 'Ignore_Error' ) THEN 1 ELSE 0 END) errorCount FROM Table group by Id 

However, when I try to do this, I get

Cannot perform aggregate function for expression containing aggregate or subquery.

What is my best way forward?

Answer 1

As indicated in the error message, you cannot use the Aggregate function on top of Sub-Query

Here is the right way to do it

 SELECT t.Id, Count(e.ErrorId) errorCount FROM Table t LEFT JOIN Errors e ON t.ErrorId = e.ErrorId AND e.ErrorCategory = 'Ignore_Error' GROUP BY t.Id 

Another way would be to use Outer Apply

 SELECT t.Id, Count(ou.ErrorId) errorCount FROM Table t OUTER apply (SELECT e.ErrorId FROM Errors e WHERE t.ErrorId = e.ErrorId AND e.ErrorCategory = 'Ignore_Error') ou GROUP BY t.id