select t1.date1, round(cnt1*1.0/cnt2, 2) from #1st sub query to get number of cancelled trip (select IFNULL(t.cnt1,0) as cnt1, r.Request_at as date1 from Trips r left join (select count(*) as cnt1, Request_at as date1 from Trips t join Users u on t.Client_Id=u.Users_Id where u.Banned="No" and (t.Status="cancelled_by_driver" or t.Status="cancelled_by_client") group by Request_at) t on r.Request_at = t.date1 group by t.date1) as t1 #2nd sub query to get number of trip within date range join (select count(*) as cnt2, Request_at as date2 from Trips t join Users u on t.Client_Id=u.Users_Id where u.Banned="No" and (Request_at="2013-10-01" or Request_at="2013-10-02" or Request_at="2013-10-03") group by Request_at) as t2 on t1.date1=t2.date2
This solution is naive but should be easy to understand
Write your MySQL query statement below
select Day, round(sum(case when RecordStatus in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) / count(*), 2)
as 'Cancellation Rate'
select A.Request_at as Day, A.status as RecordStatus
from Trips A, Users B where A.Client_id = B.Users_Id
and B.Role = 'client' and B.Banned = 'No'
and Request_at >= '2013-10-01' and Request_at <'2013-10-04'
group by Day