About count and or null

Question

I am practising my MySQL in Leetcode and I've encountered this question:

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

I have found a solution in the question's Discuss section, here it is:

select t.Request_at as Day, round(count(t.Status = 'completed' or null)/count(t.Status), 2) AS `Cancellation Rate`
from 
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

That is fine. But I want to change it to this:

select t.Request_at as Day, round(count(t.Status = 'completed')/count(t.Status), 2) AS `Cancellation Rate`
from 
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

and I get the wrong answer.

I don't understand what the or null is used for. Is it necessary? If so, why?

Answer 1

An example - http://sqlfiddle.com/#!9/9eecb7d/59602

The explanation - COUNT(<expr>) counts all non-NULL values. Comparison operators return boolean values 1(=true) or 0(=false) and both are non-NULL so both would be counted by COUNT(). When you add the or NULL part, the entire expression is still 1 for true but becomes NULL when the comparison returns false (because of the ternary logic definition used in SQL). So the COUNT() only count "trues".

Thats for standard SQL. In MySQL you can use a different trick. Because true and false are actually 1 and 0, you can just sum(t.Status = 'completed') to get the right number without using NULLs.