How to avoid DIVIDE BY ZERO error in an SQL query

Question

SELECT  YEAR, period, round((1- sum(rej_qty) / sum(recd_qty))*100, 0)   
 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

For a particular month, recd_qty is ZERO because of which I am getting DIVIDE BY ZERO error.

Is there any way to avoid DIVIDE BY ZERO error?

I there any way where in that particular month is ignored?

Adelric

Answer 1

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)) 

Hipparchus

The WHERE clause would not solve the problem as he is summing recd_qtys before dividing. – Instrumentalism

@Instrumentalism Thanks, I've changed the SQL for a subquery – Hipparchus

@Kenny That's the thing... it may not be accurate :) – Hipparchus

It would be perfectly legal for Oracle to evaluate the expression in the SELECT list before evaluating the WHERE clause, so there is no guarantee that this would eliminate the error or that a future change to the query plan wouldn't cause the error to recur. You'd need a DECODE/ CASE even if you use a subquery-- SELECT year, period, (CASE WHEN recd_sum = 0 THEN null ELSE round((1 - rej_sum/ recd_sum)*100,0) END) – Faunie

Answer 2

Have you tried using NULLIF()?

 SELECT
 ( 100 / NULLIF( 0, 0 ) ) AS value
 ;

Oracle Doc
http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php#NULLIFFunction

Another example
http://www.bennadel.com/blog/984-Using-NULLIF-To-Prevent-Divide-By-Zero-Errors-In-SQL.htm

Monocular

SELECT ( 100 / NULLIF( 0, 0 ) ) AS value FROM DUAL; to make it executable – Vanesavanessa

Answer 3

round(ISNULL(
((1- sum(rej_qty)) / NULLIF( (sum(recd_qty))*100), 0 )),
0
),0)

If you replace your division using NULLIF to set a NULL when there is divide by zero, then an ISNULL to replace the NULL with a 0 - or indeed whatever value you want it to.

Pseudo

Answer 4

CASE WHEN sum(recd_qty) <> 0 THEN round((1- sum(rej_qty) / sum(recd_qty))*100, 0) ELSE 0 END

Totipalmate