关联两张表,一个是查询10.1-10.3非禁止用户全部的数据,一个是查询10.1-10.3非禁止用户取消的数据,然后再计算取消率
select b.Day "Day",
convert(IFNULL(num1/num2,0),decimal(10,2)) AS 'Cancellation Rate'
from
(
select request_at "Day",count(id) num1
from Trips
where status !="completed"
and client_id in(select users_id from users where role="client" and banned="No")
and driver_id in (select users_id from users where role="driver" and banned="No")
and request_at between '2013-10-01' and '2013-10-03'
group by request_at
)a
right join
(
select request_at "Day",count(1) num2
from Trips
where request_at between '2013-10-01' and '2013-10-03'
and client_id in(select users_id from users where role="client" and banned="No")
and driver_id in (select users_id from users where role="driver" and banned="No")
group by request_at
) b
on a.day=b.day
order by b.day