How to deal with divide by zero error in sql

Question

I'm new to SQL and would like to know the best way of dealing with dividing by zeros. I know about the NULLIF function however I'm having a bit of trouble implementing it.

In this example, every field could be a zero. What is the best way of going about solving this; should we just check if the whole expression equates to zero or do some sort of check for each field in mu expression?

 SELECT             
        round(Sum((SOI.CurItemValue + SOI.CurTaxValue) / NULLIF(SOI.Quantity,0) * NULLIF(SOI.QuantityOutstanding,0)),2) 
        FROM SalesOrderItems SOI

With this current code I get Null value is eliminated by an aggregate or other SET operation.

I don't know about the error but when using nullif in a division like that why not have `NULLIF(SOI.Quantity,1)` instead to avoid division by zero? — Joakim Danielson, Nov 22 '18 at 09:40
@Fusiozii Using `NULLIF` to avoid the divide by zero error is correct way to go. Just put a check on your denominator to avoid having zeros. — Mayank Porwal, Nov 22 '18 at 09:46
I might be misunderstanding, but why not just add a `WHERE SOI.Quantity > 0`? — DaveyDaveDave, Nov 22 '18 at 09:50

score 2 · Accepted Answer · answered Nov 22 '18 at 09:59

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 to deal with divide by zero error in sql

1 Answers1