SQL Server Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Question

I'm having an issue with my stored procedure.

I am getting error:

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

Here's the part of my stored procedure where I believe the error occurs:

SELECT column_1, column_2,
       SUM(CASE WHEN column_2 NOT IN (SELECT product FROM table_products) THEN 1
                ELSE 0
           END) AS Total
FROM my_table 
WHERE is_rated = '1'
GROUP BY column_1, column_2 

Thank you.

Solution 1

You'll get much better performance generally if you try to avoid correlated subqueries anyway:

SELECT
    MT.column_1,
    MT.column_2,
    SUM(CASE WHEN P.product IS NULL THEN 1 ELSE 0 END) AS total
FROM
    My_Table MT
LEFT OUTER JOIN Products P ON P.product = MT.column_2
WHERE
    MT.is_rated = '1'
GROUP BY
    MT.column_1,
    MT.column_2

This assumes that there will only ever be at most one match in the Products table (Products, not Table_Products - of course it's a table so don't put that in the name). In other words, this will work if product is the PK (or an AK) of the Products table.

If that's not the case and you might have multiple matches in the Products table then you can JOIN to a subquery that uses DISTINCT on the product column.

Solution 2

SELECT
   column_1, column_2, 
   SUM(
      CASE
         WHEN table_products.product IS NULL THEN 1
         ELSE 0
      END
   ) AS Total
FROM my_table 
left join table_products on my_table.column_2 = table_products.product 
WHERE is_rated = '1'
GROUP BY column_1, column_2 

Comments

[Gordon Linoff]

This is making the assumption that Product is never repeated in either table.

[Tom H]

It's only an issue if it's repeated in the Products table - which I explicitly pointed out in my answer and even described a workaround for that case.

[Gordon Linoff]

. . I think it is an issue if it is repeated in My_Table as well -- although that does depend on the actual results the OP wants.