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

Reputation: 31

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

I am executing this query in Oracle. I have added the screenshots of my data and the returned results but the returned result is wrong. It is returning 1 but it should return 0.52. Because the customer(see in attached screenshot) have codes 1,2,4,31 and for 1,2,4 he should get 0.70 value and for 31 he should get 0.75 and then after multiplication the returned result should be 0.52 instead of 1. 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 value given to every customer on the basis of codes they got.

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

Let me be simple. If a customer have all of these codes 1, 2, 4 then he will only get 0.70 points for once, but if he have code 4 only then he will get 0.90, but if he got code 31 too then he will get extra 0.75 for having code 31. Does it make sense now?

       SELECT 
       RM_LIVE.EMPLOYEE.EMPNO, RM_LIVE.EMPNAME.FIRSTNAME,
       RM_LIVE.EMPNAME.LASTNAME, RM_LIVE.CRWBASE.BASE ,RM_LIVE.CRWCAT.crwcat AS "Rank",
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (29,721) THEN 0.25 ELSE 1 END),0),1) *
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (31,723) THEN 0.75 ELSE 1 END),0),1) *
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC = 861 THEN 0.80 ELSE 1 END),0),1) *
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (17,302,16) THEN 0.85 ELSE 1 END),0),1) *
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (3,7) THEN 0.90 ELSE 1 END),0),1)*
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (921,301,30,722,601,581) THEN 0.50 ELSE 1 END),0),1) *
       nvl(nullif(MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN (2,1, 4) THEN 0.70 ELSE 1 END),0),1) *
       nvl(nullif(MIN(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  IN (1,2) then 0 else 1 END) * 
                  MAX(CASE WHEN RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  IN (4) then 0.20 else 0 END),0),1) AS "FTE VALUE"

     FROM RM_LIVE.EMPBASE,
     RM_LIVE.EMPLOYEE,
     RM_LIVE.CRWBASE,
     RM_LIVE.EMPNAME,
     RM_LIVE.CRWSPECFUNC,
     RM_LIVE.EMPSPECFUNC,RM_LIVE.EMPQUALCAT,RM_LIVE.CRWCAT
       where RM_LIVE.EMPBASE.IDEMPNO = RM_LIVE.EMPLOYEE.IDEMPNO
        AND RM_LIVE.EMPBASE.IDCRWBASE = RM_LIVE.CRWBASE.IDCRWBASE
         AND RM_LIVE.EMPLOYEE.IDEMPNO = RM_LIVE.EMPNAME.IDEMPNO 
          AND RM_LIVE.EMPSPECFUNC.IDCRWSPECFUNC =RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC  
           AND RM_LIVE.EMPSPECFUNC.IDEMPNO =RM_LIVE.EMPLOYEE.IDEMPNO
            AND  RM_LIVE.EMPQUALCAT.IDEMPNO=RM_LIVE.EMPLOYEE.IDEMPNO 
             AND  RM_LIVE.CRWCAT.IDCRWCAT = RM_LIVE.EMPQUALCAT.IDCRWCAT
              AND RM_LIVE.CRWCAT.crwcat IN ('CP','FO','CM','MC')
               AND RM_LIVE.CRWBASE.BASE <> 'XYZ'
                AND RM_LIVE.CRWSPECFUNC.IDCRWSPECFUNC IN  
                ('921','2' ,'1','301','17','4','3','7','302' ,'861','31',
                 '723','30','722 ','29 ','721','16','601','581')  
                  AND RM_LIVE.EMPBASE.STARTDATE <= SYSDATE
                   AND RM_LIVE.EMPBASE.ENDDATE >= SYSDATE
                    AND RM_LIVE.EMPSPECFUNC.STARTDATE <= SYSDATE
                     AND RM_LIVE.EMPSPECFUNC.ENDDATE >= SYSDATE
                      AND RM_LIVE.EMPNAME.FROMDATE <=SYSDATE
                       AND RM_LIVE.EMPQUALCAT.STARTDATE <= SYSDATE
                        AND RM_LIVE.EMPQUALCAT.ENDDATE >= SYSDATE
       GROUP BY RM_LIVE.EMPLOYEE.EMPNO, RM_LIVE.EMPNAME.FIRSTNAME,
       RM_LIVE.EMPNAME.LASTNAME, RM_LIVE.CRWBASE.BASE,RM_LIVE.CRWCAT.crwcat;

My data Returned results

Upvotes: 1

Views: 3594

Answers (3)

Serg
Serg

Reputation: 22811

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;

EDIT 2 If you need multiply scores, replace + with * and convert 0 into 1.

SELECT [id]
  ,[name]
  ,r = isnull(nullif(
        max(CASE WHEN [code]  IN (1,2,4) then 100 else 0 end)
      ,0),1)
    * isnull(nullif(
       min(CASE WHEN [code]  IN (1,2) then 0 else 1 end) 
        * max(CASE WHEN [code]  IN (4) then 400 else 0 end)
      ,0),1)
    * isnull(nullif(
        max(CASE WHEN [code] IN (8) then 80 else 0 end)
      ,0),1)
  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;

Upvotes: 1

marc_s
marc_s

Reputation: 754348

You're already selecting from the testcode table - no need to do any subqueries in your CASE expression - just use this code:

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

Upvotes: 0

asktonishant
asktonishant

Reputation: 303

You missed a comma (,) before the calculating the sum.

So your code should be:

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 
            else 10 
           end) as "total" 
  FROM [Test].[dbo].[testcode] as t 
  GROUP BY id, name

Upvotes: 0

Related Questions