Divide by zero

Question

I have some formula, which returns result as 0.45:

SELECT (1-(100-ROUND(100*(itemPriceitemQ-RPrice)/(itemPriceitemQ), 2))/100)

Now I divide 1 with 0.45:
1.0/ (1-(100-ROUND(100*(itemPriceitemQ-RPrice)/(itemPriceitemQ), 2))/100)

And I get an error:
"Divide by zero error encountered."

How is that possible if I have 0.45 and not zero?
The result should be 2.222.

Answer 1

It is possible that you have some rows where itemPrice*itemQ is zero, but those rows are excluded by (for example) a WHERE clause. If the WHERE clause is done first and the calculation, everything is fine. But if the calculation is done first and then the WHERE clause, you get the Divide by zero error even though there is no row that would be returned by the query (because the WHERE clause excludes it). This is because SQL Server is allowed to process the query in any manner it believes best and so it is best to write your queries so that they cannot ever get a divide by zero error. The easiest way to do this is with the NULLIF function. So try

1.0/ (1-(100-ROUND(100*(itemPrice*itemQ-RPrice)/NULLIF((itemPrice*itemQ), 0), 2))/100)

Tom

Answer 2

Hi @simon ,

The divisor cannot be 0 in SQL Server. If the divisor is 0, an error will be returned.

There are many ways to avoid this error in SQL Server. Below we introduce three ways to deal with this error.

Method 1: SQL NULLIF Function
Tom has provided the corresponding code, please refer to it.

Method 2: Using CASE statement to avoid divide by zero error

SELECT  CASE WHEN itemPrice*itemQ=0   
        THEN NULL  
        ELSE 1.0/ (1-(100-ROUND(100*(itemPrice*itemQ-RPrice)/(itemPrice*itemQ), 2))/100) END  

Method 3: SET ARITHABORT OFF
For details, please refer to the following article.
SET ARITHABORT (Transact-SQL)

This method may cause performance problems, if not necessary, it is best to choose the first two methods.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.