CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
JM1
JM1

Reputation: 1715

TSQL - Having Clause error when trying to perform an aggregate

I have a script with some hardcoded values. I've been tasked with putting the hardcoded values into a table so the script can run even if the table values change. Instead of looking for a specific value, I'm trying to check it against a table. I have an example of the working code prior to the change and non-working code trying to sue the same logic.

I get this error:

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

Working code:

HAVING SUM (CASE WHEN Keyword = 1313 THEN 1 ELSE 0 END) = 0;

Non-working Code:

HAVING SUM (CASE WHEN Keyword IN (SELECT Keyword FROM TableB) THEN 1 ELSE 0 END) = 0;

I don't understand how to account for the table change, I am not understanding something. What do I seem to be missing about this issue and how can I get this to work? Thanks for your help.

Upvotes: 0

Views: 73

Answers (1)

Kevin UI
Kevin UI

Reputation: 292

SQL Server doesn't let you have a sub-query inside of an aggregate.

See the master, Pinal Dave: https://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-130-cannot-perform-an-aggregate-function-on-an-expression-containing-an-aggregate-or-a-subquery/

This would be a solution:

SELECT
    SUM(MyField)
FROM (
    SELECT
      CASE WHEN Keyword IN (SELECT Keyword FROM TableB) THEN 1 ELSE 0 END as MyField
    FROM YourTable
    ) subquery
HAVING SUM(MyField) = 0

Edit: (improvement from Dale K comment)

Upvotes: 2

Related Questions