How to Avoid the "Divide by Zero" Error in Sql

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.

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

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) 

Simple way to prevent a Divide By Zero error in SQL

A nicer way of doing this is to use NULLIF like this:

Percentage =  100 * ClubTotal / NULLIF(AttTotal, 0)

How to avoid DIVIDE BY ZERO error in an SQL query

If you want to ignore such records you can use a subquery

SELECT  YEAR, period, round((1- rej_sum / recd_sum)*100, 0) FROM
(
  SELECT YEAR, sum(rej_qty) rej_sum, sum(recd_qty) recd_sum
  FROM   TAB_A
  WHERE  sid = '200'
  AND    sdid IN ('4750')
  AND
  (
       (
          YEAR ='2011'
       AND    period IN('01_JAN')
       )
  OR
  (
      YEAR = '2010'
       AND    period IN ('02_FEB','03_MAR','04_APR','05_MAY','06_JUN','07_JUL','08_AUG','09_SEP','10_OCT','11_NOV','12_DEC')
       )
  )
  group by year, period
)
WHERE recd_sum <> 0;

If you want to keep them and handle the division by zero issue, you can use decode or case

SELECT  YEAR, period, DECODE(recd_qty, 0, NULL, round((1- sum(rej_qty) / sum(recd_qty))*100, 0)) 

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;

Divide by zero error error after join in sql server (that doesn't add any rows)

Use NULLIF() to prevent divide-by-zero:

select *,
       ( ([ENERGIA_NETA_SMEC_MWH] *  [CONSUMO_DE_GAS_DAM3] * 9300 * 1000) /
         NULLIF([CONSUMO_DE_GAS_DAM3] *9300 * 1000 + [CONSUMO_DE_GASOIL_M3] * 9211 +  [CONSUMO_DE_FUELOIL_T] * 10500 *1000, 0) 
       ) as [Energia generada GN MWh]
from tabla_parte tp;

If one or both the tables are views, then the problem could be occurring in the view.

The cause of your problem might be that SQL Server can re-arrange calculations, pushing them closer to where it reads the data -- and before filters are applied. This would be a particular issue if you have WHERE clauses somewhere (although these are not shown in your question).

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