How to calculate maximum of two numbers in SQL select

Question

This should be simple and shows my SQL ignorance:

SQL> select max(1,2) from dual;
select max(1,2) from dual
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

I know max is normally used for aggregates. What can I use here?

In reality I want to use somthing like

select total/max(1,number_of_items) from xxx;

where number_of_items is an integer and can be 0. I want to see total also in this case.

Answer 1

Normally it would be:

SELECT MAX(columnName)
FROM   Table1

Or

SELECT MAX(columnName)
FROM   (SELECT * FROM TableX) AS T1

Or (and this would probably be what you want in your case)

SELECT MAX(value)
FROM   (SELECT 1 AS VALUE FROM DUAL UNION SELECT 2 AS VALUE FROM DUAL)

There may be a cleaner way to do it though.

UPDATE: Using your example of number_of_items and total from table XXX, it'd be:

SELECT TOTAL/MAX(NUMBER_OF_ITEMS)
FROM   XXX

UPDATE 2: Keep in mind, if you allow number of items to be 0, you will get an exception of division by 0. That's why in the other answer the user put a case and the else was the TOTAL, this way you don't get that exception.

Answer 2

You could use a CASE statement

SELECT Total = CASE WHEN number_of_items > 0 
               THEN total/number_of_items
               ELSE total END
FROM   xxx

Answer 3

SELECT total/(CASE WHEN number_of_items>1 THEN number_of_items ELSE 1) FROM xxx

should work here....

Answer 4

It looks like you're using Oracle so you can use the greatest function for this in place of max

select total/greatest(1,number_of_items) 
from xxx;