How to avoid the divide by zero error in SQL Server

In this article, i am going to explain how to avoid divide by zero error in SQL. As we know, anytime we are dividing we need to think of the divide by zero scenario. Let’s look out below query

select
  count(sname) / count(*) as "Average"
from mytable

This will result in a division by error if there are no rows returned.

Solution 

This error can be avoided by using the NULLIF function, which returns a null value if the two passed-in expressions are equal.

Syntax of NULLIF Function :

NULLIF (aspect1, aspect2)

Here, aspect1 and aspect2 are any valid sql server expression.

select
  count(sname) / NULLIF( count(*), 0)
from mytable

So, if count(*) returns 0, a null value is returned. Any number divided by NULL gives NULL. While that isn’t a useful result but i think, it’s better than getting an error!! There you go. No more divide by zero errors in SQL.

That’s it!!…..Happy Programming...