sql query to display those with 0

I have to show drivers and nr of trips in January 2019. I also need to show those with 0 trips in January 2019. Below is the query, but it doesnt show me those with 0 trips in january 2019.

select d.driver_id, count (t.trip_id)
from drivers d left join  TRIPS t on t.DRIVER_ID = d.DRIVER_ID
left join TRIPS_PICKUP tp on tp.DESTINATION_ID = t.DESTINATION_ID
left join TRIP_ADDRESS ta on tp.DESTINATION_ID = ta.DESTINATION_ID
where t.TRIP_DATE>=to_date('2019/01/01','yyyy/mm/dd') and 
t.TRIP_DATE<=TO_DATE('2019/01/31','yyyy/mm/dd')
group by d.DRIVER_ID

I think that there is a problem in the -where- but i cant find it. I am using oracle sql developer

Answer

Move the condition to an on clause and use date literals:

select d.driver_id, count (t.trip_id)
from drivers d left join
     TRIPS t 
     on t.DRIVER_ID = d.DRIVER_ID and
        t.TRIP_DATE >= date '2019-01-01' and
        t.TRIP_DATE < date '2019-02-01' left join
     TRIPS_PICKUP tp
     on tp.DESTINATION_ID = t.DESTINATION_ID left join
     TRIP_ADDRESS ta
     on tp.DESTINATION_ID = ta.DESTINATION_ID
group by d.DRIVER_ID;

Note that I changed the upper limits for the date comparison — less than the first of January. This is particularly important in Oracle, because dates can contain a time component, even if you cannot see it when you query the table. This version is safe for data with or without a time component.