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

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

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!

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: