September 12, 2011 01:41

Typically, when you try to divide by zero in MySQL, the result will be NULL. In certain cases, however, you'd like the result to be the numerator.

For example, when posts are receiving votes, we want a post with 8 yes votes and 0 no votes to have a ratio of 8 not of NULL. The cheap solution is to add +1 to both the yes and no votes, but then the ratio will be slightly inaccurate (9 in this case). The solution is to use MySQL CASE.

SELECT `posts`.`id`, 
    (CASE `vote_no`
        WHEN 0 THEN `vote_yes`
        ELSE (`vote_yes`/`vote_no`)
    END) AS `vote_ratio`
FROM `posts`

MySQL