Alex Rivera | Logout

How to conditionally handle division by zero with MySQL

Asked 2011-11-23T16:12:15.187
23

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?

mysql sql
Edit
Report

1 Answer

16

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".

answered 2011-11-23T16:14:54.780

Your Answer