Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Error Using CASE statment in SQL Server 2008

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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:
    Code:
    select	TransNumber,
    	SUM(CASE WHEN Transkey = '8870' THEN 1 ELSE 0 END) AS Value1
    	,SUM(CASE WHEN Buttonkey IS NOT NULL THEN 1 ELSE 0 END) as Value2
    from TransDetails_201008
    	LEFT OUTER JOIN Transaction_Keys ON
    		Transkey = Buttonkey
    where Loc = 302
    group by TransNumber 
    having SUM(CASE WHEN Transkey = '8870' THEN 1 ELSE 0 END) > 0 and 
    	SUM(CASE WHEN Buttonkey IS NOT NULL THEN 1 ELSE 0 END) = 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2010
    Posts
    2
    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 .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •