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?

Kenny Cason · Answer 1 · 2011-02-17T10:51:23.150

26

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

edited Feb 17 '11 at 10:51

answered Feb 17 '11 at 10:45

Kenny Cason

12,109
11
47
72

SELECT ( 100 / NULLIF( 0, 0 ) ) AS value FROM DUAL; to make it executable – 53RT May 25 '22 at 09:01

score 10 · Accepted Answer · edited Feb 18 '11 at 21:32

10

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

edited Feb 18 '11 at 21:32

Justin Cave

227,342
24
367
384

answered Feb 17 '11 at 10:42

vc 74

37,131
7
73
89

The `WHERE` clause would not solve the problem as he is summing `recd_qty`s before dividing. – Tim Feb 17 '11 at 10:43
@Tim Thanks, I've changed the SQL for a subquery – vc 74 Feb 17 '11 at 10:50
@Kenny That's the thing... it may not be accurate :) – vc 74 Feb 18 '11 at 08:48
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)` – Justin Cave Feb 18 '11 at 21:35

score 4 · Answer 3 · answered Feb 17 '11 at 10:49

4

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.

answered Feb 17 '11 at 10:49

diagonalbatman

17,340
3
31
31

score 0 · Answer 4 · answered Oct 17 '19 at 14:37

0

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

answered Oct 17 '19 at 14:37

Rupasa Sushma

95
1
11

How to avoid DIVIDE BY ZERO error in an SQL query

4 Answers4