CODEWITHSUNDEEP
mysqlsql
Pushpendra Kuntal
Pushpendra Kuntal

Reputation: 6186

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?

Upvotes: 23

Views: 36428

Answers (7)

michal.jakubeczy
michal.jakubeczy

Reputation: 9469

Division by NULL actually works in MySQL server and returns NULL. So you can do:

SELECT ROUND(noOfBoys / NULLIF(noOfGirls, 0)) AS ration FROM student;

I think NULL ratio is the most accurate for this case. If you need the ratio to be equal to numOfBoys then you can use:

SELECT COALESCE(ROUND(noOfBoys / NULLIF(noOfGirls, 0)), noOfBoys) AS ration FROM student;

Upvotes: 2

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 383400

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

Upvotes: 5

Raffael
Raffael

Reputation: 20045

check if the denominator is zero and the nominator is not NULL - if yes then use 'NaN'.

Upvotes: 0

Ariful Islam
Ariful Islam

Reputation: 7675

select
    case student.noOfGirls
           when 0 then 1
           else  round(noOfBoys/noOfGirls)
    end as ration
from
    `student`

Upvotes: 0

itsmeee
itsmeee

Reputation: 1637

Well if you want to set noOfGirls to 1 if it's 0, then the following should work:

SELECT ROUND(noOfBoys / if(noOfGirls, noOfGirls, 1)) as ration FROM student;

Upvotes: 2

Romain
Romain

Reputation: 12819

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

Upvotes: 16

Icarus
Icarus

Reputation: 63966

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;

Upvotes: 16

Related Questions