SQL 查询在 PostgreSQL 中给出除以零错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22207567/
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
Query giving division by zero error in PostgreSQL
提问by AKIWEB
I am trying to run the following query which results me postgres error: division by zero
我正在尝试运行以下查询结果我 postgres error: division by zero
select
request_count,
response_count,
(response_count*100) / (request_count+response_count) AS proportion
from total_dummy_table;
How can I avoid and resolve the division by zeroerror?
如何避免和解决division by zero错误？
Tried to fix using the below query but getting the same result as above
尝试使用以下查询进行修复，但得到与上述相同的结果
SELECT
request_count,
response_count,
(response_count*100) / (request_count) AS proportion
FROM
total_dummy_table
ORDER BY
(response_count*100) /(CASE request_count WHEN 0 Then NULL ELSE request_count END) DESC NULLS FIRST
Please let me know where am I doing wrong, or how can I fix this. Thanks!
请让我知道我哪里做错了，或者我该如何解决这个问题。谢谢！
Result expectation:
结果预期：
The query should return me something like below:
查询应该返回如下内容：
Request_count | Response_count | Proportion
1603423 | 585706 | 36.52
Quick note:Table total_dummy_table does not have column name proportionthat is the addition to the result which have calculated proportion in it.
快速说明：表 total_dummy_table 没有列名proportion，该列名是对其中计算了比例的结果的加法。
采纳答案by Houari
select request_count,response_count,
case
when
request_count+response_count = 0 then 0
else
(response_count*100) / (request_count+response_count)
end AS proportion
from total_dummy_table;
回答by diego matos - keke
use NULLIF
使用 NULLIF
something/NULLIF(column_name,0)
NULLIF(col,val)is shorthand for
NULLIF(col,val)是简写
CASE WHEN col=val THEN NULL ELSE col
