0

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?

1

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.