CASE WHEN 求平均值

Question

sum(CASE
                  WHEN t.bipcode = 'BIP2C094' THEN
                  1
                 ELSE
                  0
               END),
sum(CASE
                  WHEN t.bipcode = 'BIP2C094' THEN
                  1
                 ELSE
                  0
               END),
 
这两个条件SUM后用CASE WHEN 求平均值怎么 写 

Answer 1

/**任务平均确认时间**/
select td.name 发起单位,
avg(case when trunc(t.create_time,'MM')=trunc(add_months(trunc(sysdate),-1),'MM') then ceil((t.update_time-t.create_time)*24*60) end) 上月平均确认时间,
avg(case when trunc(t.create_time,'MM')=trunc(sysdate,'MM') then ceil((t.update_time-t.create_time)*24*60) end) 本月平均确认时间
from response_task_info t
left join response_dept td on td.id=t.start_dept
where t.status='1'
and t.operate_status='109254'
group by td.name;

Answer 2

(sum(CASE
                  WHEN t.bipcode = 'BIP2C094' THEN
                  1
                 ELSE
                  0
               END) +
sum(CASE
                  WHEN t.bipcode = 'BIP2C094' THEN
                  1
                 ELSE
                  0
               END))/2,