Oracle SQL划分两个自定义列

Question

如果我有以下情况，请选择两种计数情况：

 COUNT(CASE WHEN STATUS ='Færdig' THEN 1 END) as completed_callbacks,
 COUNT(CASE WHEN SOLVED_SECONDS /60 /60 <= 2 THEN 1 END) as completed_within_2hours

我想把这两个结果相互设计一下，我该如何做到这一点？

这是我的尝试，但失败了：

 CASE(completed_callbacks / completed_within_2hours * 100) as Percentage

我知道这是一个相当简单的问题，但我在任何地方都找不到答案

Answer 1

您必须创建一个派生表：

SELECT completed_callbacks / completed_within_2hours * 100
FROM   (SELECT Count(CASE
                       WHEN status = 'Færdig' THEN 1
                     END) AS completed_callbacks,
               Count(CASE
                       WHEN solved_seconds / 60 / 60 <= 2 THEN 1
                     END) AS completed_within_2hours
        FROM   yourtable
        WHERE  ...)  

Answer 2

试试这个：

with x as (
  select 'Y' as completed, 'Y' as completed_fast from dual
  union all
  select 'Y' as completed, 'N' as completed_fast from dual
  union all
  select 'Y' as completed, 'Y' as completed_fast from dual
  union all
  select 'N' as completed, 'N' as completed_fast from dual
)
select 
sum(case when completed='Y' then 1 else 0 end) as count_completed,
sum(case when completed='N' then 1 else 0 end) as count_not_completed,
sum(case when completed='Y' and completed_fast='Y' then 1 else 0 end) as count_completed_fast,
case when (sum(case when completed='Y' then 1 else 0 end) = 0) then 0 else
  ((sum(case when completed='Y' and completed_fast='Y' then 1 else 0 end) / sum(case when completed='Y' then 1 else 0 end))*100)
end pct_completed_fast
from x;

结果：

"COUNT_COMPLETED"   "COUNT_NOT_COMPLETED"   "COUNT_COMPLETED_FAST"  "PCT_COMPLETED_FAST"
3   1   2   66.66666666666666666666666666666666666667

诀窍是使用SUM而不是COUNT，以及解码或CASE。

Answer 3

select
   COUNT(CASE WHEN STATUS ='Færdig' THEN 1 END) 
  /
   COUNT(CASE WHEN SOLVED_SECONDS /60 /60 <= 2 THEN 1 END) 
  * 100 
  as 
   Percentage