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

I have the following query which works fine:

SELECT MethId,
       SUM(CASE
           WHEN MethId IN (1,4,5,7,9) THEN 1
            ELSE 0
          END) Total, -- HERE    
       SUM(CASE
           WHEN (MethId IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31,56,59,67,68,74,110,182) ) THEN 1
              ELSE 0
          END) TotalBottl 
        from TblMeth
       group by MethId

Instead of hardcoding the values for Total, I like to pull it from a table. When I do the following I get the following error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    SELECT MethId,
       SUM(CASE
           WHEN MethId IN (Select MethId from methInfoTbl) THEN 1
            ELSE 0
          END) Total, -- HERE    
       SUM(CASE
           WHEN (MethID IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31,56,59,67,68,74,110,182) ) THEN 1
              ELSE 0
          END) TotalBottl 
        from TblMeth
       group by MethId

CodePudding user response：

As MethId is unique within methInfoTbl the you can use a LEFT JOIN and then COUNT the rows in the JOINed table:

SELECT MT.MethId,
       COUNT(mIT.MethId) Total,
       COUNT(CASE WHEN MT.MethId IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31,56,59,67,68,74,110,182) THEN 1 END) TotalBottl 
FROM dbo.TblMeth MT --Don't prefix your objects with what they are, call the table "Meth", you know its a table.
     LEFT JOIN dbo.methInfoTbl mIT ON MT.MethId = mIT.MethId --Why does have tbl as a suffix instead? If you "have" to use suffixes/prefixes (you don't) then at least be consistenr
GROUP BY MT.MethId;