Fari Fari - 7 months ago 35
SQL Question

SQL Error "Cannot perform an aggregate function on an expression containing an aggregate or a sub query."

I am executing this query in SQL server but do not know why this error happens:


Cannot perform an aggregate function on an expression containing an aggregate or a sub query


I am really stuck here. Please help me. I will be very thankful to you.

Here is my query. What I actually want to do is I want to calculate points given to every customer on the basis of codes they got.

If a customer have code = 1 then he will get 100 points and then if he have code = 2 and 4 too then I do not want to give him extra 100 for code 2 and 4.

Let me be simple. If a customer have all of these codes 1, 2, 4 or even if he have only one of them then he will only get 100 points for once but if he got code 8 too then he will get extra 80 for having code 8. Does it make sense now?

SELECT
[id], [name],
SUM(CASE
WHEN (SELECT [code]
FROM [Test].[dbo].[testcode] AS ts
WHERE ts.id = t.id) IN (1, 2, 4)
THEN 100
WHEN (SELECT [code]
FROM [Test].[dbo].[testcode] AS ts
WHERE ts.id = t.id) IN (8)
THEN 80
END) AS "total"
FROM
[Test].[dbo].[testcode] AS t
GROUP BY
id, name


screenshot of my data

Answer

According to desired reasult comment, try this

SELECT [id]
  ,[name]
  , r = max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
    + max(CASE WHEN [code] IN (8) then 80 else 0 end)
  FROM 
    -- your table here
    (values (1, 'ali',4)
            ,(1, 'ali',1)
            ,(1, 'ali',8)
    ) as t(id, name,code)
  GROUP BY id, name;

EDIT another story for excluding something. Any of 1,2,4 give 100 plus if it was only 4 without (1,2) add 400.

SELECT [id]
  ,[name]
  , r = max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
    + min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
        * max(CASE WHEN [code]  IN (4) then 400 else 0 end)
    + max(CASE WHEN [code] IN (8) then 80 else 0 end)
  FROM 
    -- your table here
    (values (1, 'ali',4)
            ,(1, 'ali',1)
            ,(1, 'ali',8)
            ,(2, 'ali',4)
            ,(2, 'ali',8)
    ) as t(id, name,code)
  GROUP BY id, name;
Comments