SQL Divide by Zero Fix
Post # 72 permalink Topic #72 by mreschke on 2008-05-30 10:11:25 (viewed 2,072 times)

There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows:

  • CASE statement
  • NULLIF/ISNULL functions
  • SET ARITHABORT OFF and SET ANSI_WARNINGS OFF

Case Statement[-][- -][++]

Using the CASE statement, your query will look like the following:

Code Snippet
SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]

If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed.

NULLIF and ISNULL[-][- -][++]

Using the NULLIF and ISNULL functions, your query will look like the following:

Code Snippet
SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]

What this does is change the denominator into NULL if it is zero. Then in the division, any number divided by NULL results into NULL. So if the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, the ISNULL function is used.

ARITHABORT and ANSI_WARNINGS[-][- -][++]

Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following:

Code Snippet
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT [Numerator] / [Denominator]

With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error. To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function:
Code Snippet
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT ISNULL([Numerator] / [Denominator], 0)

Recources [-][- -][++]