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

Question

Error:

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

The part where the error occurred:

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 

Can someone please help me with this?

Answer 1

In general, if you strive to prevent correlated subqueries, your performance will be substantially better:

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 is based on the supposition that there will never be more than one match in the Products table (Products, not Table Products – it's a table, so don't call it that). In other words, if the product is the PK (or an AK) of the Products table, then this will work.

If not, and there may be several matches in the Products database, you can JOIN to a subquery that makes use of the DISTINCT operator on the product field.

Answer 2

need answer for this