CODEWITHSUNDEEP
phpmysqldivide-by-zero
omega
omega

Reputation: 43873

How to handle division by 0 for select statements?

I do this

    $query4 = "(
                 SELECT count(*) 
                 FROM checklist c
                 JOIN task AS t ON t.id = c.task_id AND t.active=1
                 WHERE c.placement_id = m.id
               ) / (
                 SELECT count(*) 
                 FROM task 
                 WHERE active = 1
               )";

in php which I stick in a bigger sql string. The problem is the second select above, may be 0. In the case that it is 0 I want the division to be automatically a 1 (like turn the whole query4 into a 1).

Does anyone know how to do this?

Thanks.

Upvotes: 4

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

In your case, the simplest change is probably this:

$query4 = "(
             SELECT count(*) 
             FROM checklist c
             JOIN task AS t ON t.id = c.placement_id AND t.active=1
             WHERE c.placement_id = m.id
           ) / (
             SELECT (case when count(*) > 0 then count(*) end)
             FROM task 
             WHERE active = 1
           )";

This will replace 0 values with NULL -- so the division will return NULL instead of an error.

Upvotes: 3

Related Questions