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

3
votes

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.

3
sqlsql-servertsql
I don't think this query does what you expect it to do. It will either return # products in table_products or one less than the number (or 0 if product is ever NULL). You may want to ask another question with sample data and desired results. - Gordon Linoff

3 Answers

7
votes

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.

2
votes
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
0
votes

You can move the SUM() logic inside the subquery:

SELECT t.column_1, t.column_2,
       (SELECT COUNT(*)
        FROM table_products p
        WHERE t.column_2 <> p.product
       ) as Total
FROM my_table t
WHERE t.is_rated = '1'
GROUP BY t.column_1, t.column_2 ;