|
|
Error Message
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Causes:
This error is caused by performing a division operation wherein the denominator or the divisor is 0. This error is not encountered when the denominator or divisor is NULL because this will result to a NULL value.
|
Solution / Work Around:
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
Using the CASE statement, your query will look like the following:
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.
Using the NULLIF and ISNULL functions, your query will look like the following:
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.
Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following:
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:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT ISNULL([Numerator] / [Denominator], 0)
|