Avoid SQL Divide-by-zero errors by using NULLIF
Posted by jrotenstein on October 6, 2009
I picked up this tip while reading SQL Hacks.
Let’s say you’re receiving occasional divide by zero errors:
select count(name) / count(*) as "Average" from xyz
This will result in a
division by error if there are no rows returned.
It can be avoided by using the
NULLIF function, which returns a
null value if the two passed-in expressions are equal:
select count(name) / NULLIF( count(*), 0) from xyz
count(*) = 0, a
null value is returned. The resulting division therefore equates to
null. While that isn’t a particularly useful result, it’s better than getting an error!