When writing queries that perform lots of calculations, it is very easy to forget to ensure that our queries are set up to avoid the “Division by zero encountered” error.
Luckily, there are three easy workarounds to avoid getting this ugly error in our SELECT statements:
- 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 in the above query, 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)
The post Avoiding the SQL “Division by Zero Encountered” Error appeared first on OptimusBI.