0

I need your help. I'm trying to calculate percentages by using results from JOINING divided by the original value. I do not know how to combine both values. I try to find trips with station id which do not exist in the table station and then find the percentage of that trips.

I only can write the query to this extend:

SELECT COUNT(*)
FROM (SELECT a.start_station_id, b.station_id, a.end_station_id,a.bikeid
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b 
ON a.start_station_id = b.station_id
WHERE b.station_id IS NULL)

I need to divide the result COUNT from the query above with COUNT(*) before JOIN.

Kindly help me with this.

Thank you!!

0

One way to get expected output can be

select case when total_count = 0 then 0.0 else count_1*100.0/total_count end as pcnt 
from 
(
SELECT count(case when b.statition_id is null then 1 end) as count_1, 
count(*) as total_count 
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b 
ON a.start_station_id = b.station_id
)x 

Note, it returns 0.0 if total_count is 0 to avoid division by zero. You may want to return null(casted to proper decimal type ) in such case instead.

1
  • Thank you so much for your help
    – snsd94
    Feb 3 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.