CODEWITHSUNDEEP
sqlsql-servert-sql
Clem_Fandango
Clem_Fandango

Reputation: 364

Using NULLIF to divide by zero

This is the error I'm receiving below:

Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

Having looked around at solutions it looks like I need to use NULLIF or ISNULL, I'm just unsure as to how to include it within the line below.

select  
min(round(OnCallTime*100/TotalTime,1)) as total

I'm using SQL Management Studio 2012

Upvotes: 4

Views: 3001

Answers (4)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use coalesce() which return 1st non null value

select min(round((OnCallTime)*100/coalesce(TotalTime,1),1)) as total

Upvotes: 0

George Joseph
George Joseph

Reputation: 5922

So in the event TotalTime=0 what do you want to display?. I suggest NULL since divide by zero is not defined?.

You can write the query as follows

select  
       min(round( 
                  (OnCallTime*100)
                  / (case when TotalTime =0 then null else TotalTime end)
                ,1
                )
            ) as total

Upvotes: 1

Mayank Porwal
Mayank Porwal

Reputation: 34056

Use NULLIF in denominator like below:

select  
min(round((OnCallTime*100/NULLIF(TotalTime,0)),1)) as total

So, whenever TotalTime is zero, it'll be replaced by NULL and you will not get the error of Division by Zero.

Upvotes: 5

Fahmi
Fahmi

Reputation: 37473

use coalesce() function 

select  
min(round(OnCallTime*100/coalesce(TotalTime,1),1)) as total

Upvotes: 0

Related Questions