How to avoid DIVIDE BY ZERO error in an SQL query

jai picture jai · Feb 17, 2011 · Viewed 56.4k times · Source
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?

Answer

Kenny Cason picture Kenny Cason · Feb 17, 2011