Division produces unexpected results

Question

id  client_id   driver_id   city_id status  request_at
1   1   10  1   completed   2023-12-23
2   2   11  1   cancelled_by_driver 2023-12-23
3   3   12  6   completed   2023-12-23
4   4   13  6   cancelled_by_client 2023-12-23
5   1   10  1   completed   2023-12-24
6   2   11  6   completed   2023-12-24
7   3   12  6   completed   2023-12-24
8   2   12  12  completed   2023-12-25
9   3   10  12  completed   2023-12-25
10  4   13  12  cancelled_by_driver 2023-12-25
11  4   13  12  completed   2023-12-26
11  4   13  12  cancelled_by_driver     2023-12-27

I have a table above.Then I wrote a code as below:

SELECT count(*) as count_total, 
  count(*) filter (where status like 'completed%') as count_completed,
  count(*) filter (where status like 'cancelled%') as count_cancelled,
  request_at as Date
  FROM rides 
 GROUP BY Date

I got the table below

count_totalcount_completedcount_cancelledDate
4   2   2   2023-12-23
1   1   0   2023-12-26
3   3   0   2023-12-24
1   0   0   2023-12-27
3   2   1   2023-12-25

then I wrote the code below

with counted_status as (SELECT count(*) as count_total, 
  count(*) filter (where status like 'completed%') as count_completed,
  count(*) filter (where status like 'cancelled%') as count_cancelled,
  request_at as Date
  FROM rides 
 GROUP BY Date)
select Date, round(count_cancelled/count_total*100,2) as Cancellation_rate
from counted_status
where Date >= '2023-12-23' and Date <= '2023-12-25'

somehow I got the results like this:

DateCancellation_rate
2023-12-23  0.00
2023-12-24  0.00
2023-12-25  0.00

Can anyone tell me why?

Answer 1

I think you are trying to print the correct Cancellation_rate. But you have to change the type of a variable to float for it to work.

select Date, round(((count_cancelled * 1.0)/count_total)*100, 2) as Cancellation_rate
from counted_status where Date >= '2023-12-23' and Date <= '2023-12-25'

Replace the last statement with this.

Answer 2

with counted_status as (SELECT count(*) as count_total, 
  count(*) filter (where status like 'completed%') as count_completed,
  count(*) filter (where status like 'cancelled%') as count_cancelled,
  request_at as Date
  FROM rides 
JOIN users
  on users.user_id = rides.client_id
  where users.banned = 'No'  
 GROUP BY Date)
select Date, round((count_cancelled::NUMERIC /count_total::NUMERIC)*100, 2) as Cancellation_rate
from counted_status where Date >= '2023-12-23' and Date <= '2023-12-25'

thanks Frank Heikens and Ajex for remind to change the variable type. I add ::numeric and it works.