[SOLVED] How to deal with divide by zero error in sql

How to deal with divide by zero error in sql

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.

Solution

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