ORACLE – Trying to return MAX or MIN based on a CASE statement

caseoracle

I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2.

SELECT  
        CASE mod(ROLE_TYPE, 2)
            WHEN 1 THEN MAX(SALARY)
            ELSE MIN(SALARY)
            END
FROM HR;

Is this possible? I get an error when I run this:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

My guess is that case can't handle aggregate functions like this. Is there a way to do this in a single select statement so I don't have to do a IF/THEN SELECT?

Best Answer

When you want to display aggregate results for each row without grouping, use the analytic versions of standard aggregate functions. For example:

SELECT  
  CASE mod(ROLE_TYPE, 2)
    WHEN 1 THEN MAX(SALARY) OVER ()
    ELSE MIN(SALARY) OVER ()
  END
FROM HR;
Related Question