I am trying to view most frequent route from 2 table filtered by casual user only and column route that is not null. My code work but it only count route on table 1 and null still shown in route column even though I already add not null in where clause. I have tried full outer join and union all but the problem still persist. Please help. Thank you
I tried full outer join but table 2 not included and there's still null even though I filter not null by where clause.
SELECT
CONCAT(trip1.start_station_name,' to ',trip1.end_station_name) AS route, COUNT(CASE WHEN trip1.member_casual = 'casual' OR trip2.member_casual ='casual' THEN 1 END) AS number_of_rides
FROM coursera-project-on-big-query.Divvy_trip_data.Divvy_trip_2022_01 AS trip1
FULL OUTER JOIN
coursera-project-on-big-query.Divvy_trip_data.Divvy_trip_2022_02 AS trip2 ON trip1.ride_id = trip2.ride_id
WHERE 'start_station_name' IS NOT NULL
AND 'end_station_name' IS NOT NULL
GROUP BY route
ORDER BY number_of_rides DESC
LIMIT 1000
I tried union all but table 2 still not included and there's still null in route column even though I add where clause.
SELECT
CONCAT(start_station_name,' to ',end_station_name) AS route, COUNT() AS number_of_rides
FROM coursera-project-on-big-query.Divvy_trip_data.Divvy_trip_2022_01 AS trip1
WHERE
member_Casual = 'casual' AND
'route' IS NOT NULL
GROUP BY route
UNION ALL
SELECT
CONCAT(start_station_name,' to ',end_station_name) AS route, COUNT() AS number_of_rides
FROM coursera-project-on-big-query.Divvy_trip_data.Divvy_trip_2022_02 AS trip2
WHERE
member_Casual = 'casual' AND
'route' IS NOT NULL
GROUP BY route
ORDER BY number_of_rides DESC
LIMIT 100
Result from table 1 and table 2 is different. I don't know why
I expect that sql count both table filtered by casual user only and track the most frequent route they take and not having null displayed in column route
