CODEWITHSUNDEEP
sqlsql-serversubquery
Loofer
Loofer

Reputation: 6965

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

I have a query 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 hardcoded list of ids and I have a simple query 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 this I get

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

What is my best way ahead?

Upvotes: 6

Views: 690

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

As stated in error message you cannot use Aggregate function on top of Sub-Query

Here is the correct 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 will be using 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

Upvotes: 3

Related Questions