How to conditionally handle division by zero with MySQL

Question

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?

Answer 1

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;