sql - 当行可以包含 0 时计算百分比

Question

我之前问过以下问题:Calculating percentage within the SQL

现在我很幸运得到了一个很好的答案，但是在我的问题中我没有考虑到我的行可能包含零。

问题行很简单:

select 
Number_of_Calls,
Number_of_Answered,
((Number_of_answered/Number_Of_Calls)*100) as answer_percent,
Date
from table Where Date BETWEEN '2012-09-10' AND '2012-09-11'

但是 Number_of_answered 可能为零(如果我们公司没有接听电话)所以如果我想要百分比的完整数字，我该如何考虑 0？

顺便说一下。我们使用的数据库是带有 PL SQL 的 Oracle 数据库

Answer 1

nullif 函数可用于在除数为零时返回 null。当 SQL 遇到空除数时，它会强制整个方程返回空值，从而防止错误发生。

select 
Number_of_Calls,
Number_of_Answered,
((Number_of_answered/nullif(Number_Of_Calls,0))*100) as answer_percent,
Date
from table Where Date BETWEEN '2012-09-10' AND '2012-09-11'

如果您想排除空百分比，请使用以下 SQL:

select * from(select 
    Number_of_Calls,
    Number_of_Answered,
    ((Number_of_answered/nullif(Number_Of_Calls,0))*100) as answer_percent,
    Date
    from table Where Date BETWEEN '2012-09-10' AND '2012-09-11') alias
where answer_percent is not null;