Q:

How to conditionally handle division by zero with MySQL

In MySQL, this query might throw a division by zero error:
SELECT ROUND(noOfBoys / noOfGirls) AS ration
FROM student;

If noOfGirls is 0 then the calculation fails.
What is the best way to handle this?  
I would like to conditionally change the value of noOfGirls to 1 when it is equal to 0. 
Is there a better way?

A:

Yes, you can do a case:
select case when noOfGirls=0 then noOfBoys 
       else  round(noOfBoys/noOfGirls) end as ration 
from student;

But you probably want:
select case when noOfGirls=0 then 1 
       else  round(noOfBoys/noOfGirls) end as ration 
from student;

A:

You can use this (over-expressive) way:
select IF(noOfGirls=0, NULL, round(noOfBoys/noOfGirls)) as ration from student;

Which will put out NULL if there are no girls, which is effectively what 0/0 should be in SQL semantics.
MySQL will anyway give NULL if you try to do 0/0, as the SQL meaning fo NULL is "no data", or in this case "I don't know what this value can be".

A:

GREATEST(x, 1)
Can be used since the number of girls must be positive:
SELECT ROUND(noOfBoys / GREATEST(noOfGirls, 1)) AS ration FROM student;

But it is not very portable across SQL implementations.
Documentation: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest

