Hello Everyone,

I have been trying to run the below query and keep getting the error message

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

I think its got to do with the second case statement, but I'm unsure what I have done wrong.

Can someone also let me know if my Count syntax is correct ?



select
TransNumber,
SUM(CASE WHEN Transkey = '8870' THEN 1 ELSE 0 END) AS Value1,
SUM(CASE WHEN Transkey IN (select Buttonkey from Transaction_Keys) THEN '1' ELSE '0' END) as Value2
from TransDetails_201008
where Loc = 302
group by TransNumber having (Count (Value1) >0 and (COUNT(Value2)=0)


Any help is greatly appreciated.

thanks,
vr

Not sure what you wanted to get from "SUM(CASE WHEN Transkey IN (select Buttonkey from Transaction_Keys) THEN '1' ELSE '0' END) as Value2". A long string of 1's and 0's? like a binary number? I consider it as a typo, and that you wanted to sum the numeric values 1 or 0.

The calculated columns Value1 and Value2 are not variables like in a programming language. If you want to do something with them in the HAVING clause, you will have to repeat the calculations.

"group by TransNumber having (Count (Value1) >0 and (COUNT(Value2)=0)" I guessed you wanted only those where the column Value1 > 0 and the column Value2 equals 0.

Try this:

Thanks for the response Wim.

For the below Case statement

SUM(CASE WHEN Transkey IN (select Buttonkey from Transaction_Keys) THEN '1' ELSE '0' END) as Value2

I wanted to show 1 if the Transkey matched any of Button keys from the Transaction_Keys table else I wanted to be show as a 0 .