CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Fari
Fari

Reputation: 31

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

I was executing this query in SQL but is giving me an error like this "Cannot perform an aggregate function on an expression containing an aggregate or a sub query." Is there anyone who can help me with it. I am really stuck. Thank you Here is my query. 

SELECT [id]
      ,[name]
      ,SUM(case 
           when [code] = 1  Then 
           case 
             when exists(select * from [sampletab] where [id] = [id]and [code] = 2) then

                case when exists(select * from [sampletab] where [id] = [id] and [code] = 4) then 100 
                else 100
                end 

               else 100
               end    
           when [code] = 8 then 200
           when code = 2 then 100
           when code = 4 then 100

      end
      ) as "totl"

  FROM [test].[dbo].[sampletab]
  GROUP BY id , name

My data

Upvotes: 0

Views: 174

Answers (1)

BenM
BenM

Reputation: 104

Here's a version of your query that should actually get some results. 

SELECT
Id,
Name,
SUM(ScorePerCode)
FROM (
    SELECT 
    id,
    name,
    CASE WHEN code = 1 THEN 100
        WHEN code = 2 THEN 50
        WHEN code = 4 THEN 20
        WHEN code = 8 THEN 200
        END AS ScorePerCode
    FROM [test].[dbo].[sampletab]
) X
GROUP BY Id, Name

This section of your query is meaningless, and is the source of your problems. You're always going to arrive at a value of 100, if this query ever stood a chance of working...

 WHEN [code] = 1
         THEN 
            CASE WHEN EXISTS ( SELECT * FROM [sampletab] WHERE [code] = 2 ) 
            THEN 
                CASE WHEN EXISTS ( SELECT * FROM [sampletab] WHERE [code] = 4) 
                THEN 100
                ELSE 100 END 
            ELSE 100 END

Upvotes: 0

Related Questions