title: SQL Error "Cannot perform an aggregate function on an expression containing an aggregate or a sub query."
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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?</p>
  
  <pre><code>       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 &lt;&gt; '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 &lt;= SYSDATE
                     AND RM_LIVE.EMPBASE.ENDDATE &gt;= SYSDATE
                      AND RM_LIVE.EMPSPECFUNC.STARTDATE &lt;= SYSDATE
                       AND RM_LIVE.EMPSPECFUNC.ENDDATE &gt;= SYSDATE
                        AND RM_LIVE.EMPNAME.FROMDATE &lt;=SYSDATE
                         AND RM_LIVE.EMPQUALCAT.STARTDATE &lt;= SYSDATE
                          AND RM_LIVE.EMPQUALCAT.ENDDATE &gt;= SYSDATE
         GROUP BY RM_LIVE.EMPLOYEE.EMPNO, RM_LIVE.EMPNAME.FIRSTNAME,
         RM_LIVE.EMPNAME.LASTNAME, RM_LIVE.CRWBASE.BASE,RM_LIVE.CRWCAT.crwcat;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/b2eKj.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b2eKj.jpg" alt="My data"></a>
  <a href="https://i.stack.imgur.com/St9fy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/St9fy.jpg" alt="Returned results"></a></p>