count和case when结合的用法

 

 题目链接：262. 行程和用户 - 力扣（LeetCode）

select Request_at as `Day`,
round(count(case when Status != 'completed' then Id end)/count(*),2) as `Cancellation Rate`
from Trips
where Client_Id not in( select Users_Id as Client_Id from Users where Banned = 'Yes')
and Request_at between '2013-10-01' and '2013-10-03'
group by Request_at

注意这里的count和case结合的用法。然后就是round取小数位数的用法。

---

 用这种sum的方法也很好：

select query_name,
        round(avg(rating/position),2) quality,
        round(sum(case when rating<3 then 1 else 0 end)/count(*),4)*100 poor_query_percentage
from queries
group by query_name;