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.

Answer 1

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