MySQL: Count(*) NULL -> 0 with right join

Question

I'm close to finishing a LeetCode hard problem. The idea is find the cancellation rate (percent) for a date range given that neither the driver or the client has been banned from a ride sharing service.

The problem I'm running into is that on October 2nd, there were no cancellations; using an inner join, this date is omitted. Using a right join, the value is returned as NULL. I need this NULL value to be mapped to 0.

The problem: https://leetcode.com/problems/trips-and-users/

My code:


SELECT output2.day AS "Day", ROUND(output1.failed/output2.success,2) AS "Cancellation Rate"
FROM

((SELECT t1.request_at AS Day, COUNT(*) AS failed
FROM Trips T1
WHERE T1.Status != 'completed'
AND (T1.request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND T1.client_id IN 
  (SELECT users_id 
   FROM users 
   WHERE role = 'client'
   AND banned = 'No')
AND t1.driver_id IN
    (SELECT users_id 
   FROM users U2 
   WHERE role = 'driver'
   AND banned = 'No')

GROUP BY t1.request_at
) AS output1

RIGHT JOIN

(SELECT t2.request_at AS Day, COUNT(*) AS success
FROM Trips T2
WHERE (T2.request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND T2.client_id IN 
  (SELECT users_id 
   FROM users 
   WHERE role = 'client'
   AND banned = 'No')
AND t2.driver_id IN
    (SELECT users_id 
   FROM users 
   WHERE role = 'driver'
   AND banned = 'No')

GROUP BY t2.request_at
) AS output2

ON output1.Day = output2.Day)

GROUP BY output2.day

My Output:

{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", null], ["2013-10-03", 0.50]]}

Desired Output:

{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.00], ["2013-10-03", 0.50]]}

I've seen various community recommendations, such as using the functions ISNULL or IFNULL. But both returned an error. Any ideas?

GMB · Accepted Answer · 2020-03-23T19:07:08.720

You could just use COALESCE() in the most outer query to turn NULL values to 0s:

SELECT 
    output2.day AS "Day", 
    COALESCE(ROUND(output1.failed/output2.success,2), 0) AS "Cancellation Rate"
FROM ...

Note, however, that you query could be largely simplified by using joins and conditional aggregation. Something like this should be close to what you want:

select 
    t.request_at as day, 
    avg(t.status != 'completed') as cancellation_rate
from trips t
inner join users uc 
    on uc.users_id = t.client_id and uc.role = 'client' and uc.banned = 'no'
inner join users ud 
    on ud.users_id = t.client_id and ud.role = 'driver' and ud.banned = 'no'
where t.request_at between '2013-10-01' and '2013-10-03'
group by t.request_at

MySQL: Count(*) NULL -> 0 with right join

1 Answers1