SQL Brain

This is your Brain on SQL

  • About this Blog

    I use PostgreSQL and love it a lot.

    Whenver I discover or conjeur something neat, I post it here!

  • Subscribe

  • Advertisements

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:

  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:

  count(name) / NULLIF( count(*), 0)
from xyz

So, if 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!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: