Using Nullif to Divide by Zero

How to avoid the "divide by zero" error in SQL?

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

Using NULLIF to divide by zero

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.

How to deal with divide by zero error in sql

you can use CASE to calculate only if the Quantity greater than 0. Check this query :

SELECT             
    CASE WHEN SOI.Quantity=0 THEN 0
         ELSE ROUND( Sum( (SOI.CurItemValue + SOI.CurTaxValue) / SOI.Quantity  * SOI.QuantityOutstanding ),2) END OutstandingValue
FROM SalesOrderItems SOI

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

Rather than the CASE, I usually opt for NullIf() on the denominator

Example

select id_date,
       id_company,
       id_kpi,
       sum(CASE  WHEN id_kpi=50 THEN -actual_mes END) /
       nullif(sum(CASE  WHEN id_kpi=51 THEN actual_mes END),0) Amount
  from dual;

i used nullif and got sql divide by zero error

Your problem is that when ToplamSatis happens to be zero, you are still dividing by zero:

NULLIF(ToplamSatis, 0.1)

The above would replace ToplamSatis with 0.1, but only if the former were NULL, not if it were zero. Try the following CASE logic:

CASE WHEN ToplamSatis = 0
     THEN ISNULL((ToplamStok / 0.1)*7, 0)   -- not sure if need to wrap with ISNULL
     ELSE (ToplamStok / ToplamSatis)*7
END

How can prevention of Divide by zero error encountered in sql server?

Use NULLIF to handle Divide by zero error.

Then use ISNULL to make the value as 100%

SELECT y.Telno,
       t.Cycle + '-' + y.Cycle                                                   Cycle,
       ( Isnull(( ( y.CurBill - t.CurBill ) / NULLIF(t.CurBill, 0) ), 1) * 100 ) Price
FROM   [ClubEatc].[dbo].[GetOnlineBills] y
       INNER JOIN [ClubEatc].[dbo].[GetOnlineBills] t
               ON y.Telno = t.TelNo
                  AND Cast(y.Cycle AS INT) - 1 = Cast(t.Cycle AS INT) 

Divide by zero error encountered when using datediff

Depending on your requirements and schema, you can treat such values as NULL

UPDATE T_SG_WICA_RISK_DETAILS   
SET EarnedPercentage = case
  when Policy_ExpiryDate <  (getdate()-1) then '1'
  else datediff(day, Policy_EffectiveDate,(getdate()-1)) /
    NULLIF(datediff(day, Policy_EffectiveDate,Policy_ExpiryDate), 0)
end

Note the NULLIF() in the divisor. It will produce NULL when the DATEDIFF produces the value 0 and since arithmetic expressions containing NULL will yield to NULL, your division will end up being NULL.

Using the above, you can have any value if NULL as a result is not useful by wrapping the whole expression in the ELSE into an ISNULL():

UPDATE T_SG_WICA_RISK_DETAILS   
SET EarnedPercentage = case
  when Policy_ExpiryDate <  (getdate()-1) then '1'
  else ISNULL(datediff(day, Policy_EffectiveDate,(getdate()-1)) /
    NULLIF(datediff(day, Policy_EffectiveDate,Policy_ExpiryDate), 0), 0)
end

How to handle Divide by zero Error in SQL query

Use nullif(price,0)

Select id, description, cost, price, (price-cost)/nullif(price,0) as Margin from item

---

Related Topics