如何避免 SQL 查询中的 DIVIDE BY ZERO 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5027808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid DIVIDE BY ZERO error in an SQL query
提问by jai
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.
对于特定月份，recd_qty 为零，因此我收到 DIVIDE BY ZERO 错误。
Is there any way to avoid DIVIDE BY ZERO error?
有没有办法避免除零错误？
I there any way where in that particular month is ignored?
我有什么办法在那个特定的月份被忽略吗？
采纳答案by vc 74
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
如果您想保留它们并处理除以零问题，您可以使用 decode 或 case
SELECT YEAR, period, DECODE(recd_qty, 0, NULL, round((1- sum(rej_qty) / sum(recd_qty))*100, 0))
回答by Kenny Cason
Have you tried using NULLIF()?
你试过使用NULLIF()吗？
SELECT
( 100 / NULLIF( 0, 0 ) ) AS value
;
Oracle Doc
http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php#NULLIFFunction
Oracle 文档
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
另一个例子
http://www.bennadel.com/blog/984-Using-NULLIF-To-Prevent-Divide-By-Zero-Errors-In-SQL.htm
回答by diagonalbatman
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.
如果您在除以零时使用 NULLIF 替换您的除法以设置 NULL，则使用 ISNULL 将 NULL 替换为 0 - 或者实际上是您想要的任何值。
回答by Rupasa Sushma
CASE WHEN sum(recd_qty) <> 0 THEN round((1- sum(rej_qty) / sum(recd_qty))*100, 0) ELSE 0 END
CASE WHEN (recd_qty) <> 0 THEN round((1- sum(rej_qty) / sum(recd_qty))*100, 0) ELSE 0 END
