CODEWITHSUNDEEP
sqlsql-servert-sql
jomsk1e
jomsk1e

Reputation: 3625

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

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.

Upvotes: 3

Views: 43422

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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 ;

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

Upvotes: 2

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 7

Related Questions