NULLIF() to prevent divide by zero errors in SQL

We encountered problems trying to dividing tru zero. We tried to select a duration in day's and the amount of visits, to calculate the avarage staying periode. When a customer stays shorter than one day, 0 is registerd, resulting in a 0 divided by 0, which obvious results in error.

i.e.

Duration = 5

Amount = 0

Select (5/0) gives an error.

We could write an case statement to prevent this, but there is an easier way: NULLIF!

NULLIF() takes two arguments and returns NULL, if the two values are the same. This can be used to turn the divisor from a zero into a NULL, which will force the entire equation to become NULL.

Duration = 5

Amount = 0

SELECT

( Duration / NULLIF( 0, Amount ) ) = ( 5 / NULLIF( 0, 0 ) )

This statement returns NULL and can be very helpful when working with aggregate or calculated values.