聚合case when之后的字段

Question

count(
case
when result = 'right' then 1
else null
end
) / count(q2.question_id) as rightrate
这样可以跑出结果，但是我命名了条件分支的字段
count(
case
when result = 'right' then 1
else null
end as right_cnt
) / count(q2.question_id) as rightrate
这样就不行了
就是count right_cnt 这个字段

Answer 1

别名得放在外边，如果你想既要count，又要比率的话这样

count(
case
when result = 'right' then 1
else null
end
)  as right_cnt，
count(
case
when result = 'right' then 1
else null
end
) / count(q2.question_id) as rightrate

计数的话我觉得case when不成立的else 为0比较好呢
count(
case
when result = 'right' then 1
else 0
end
) as right_cnt，