I keep getting "No matching signature for operator = for argument types: STRING, INT64. Supported signature: ANY = ANY at [18:54] in Big Query

Question

SELECT
  station_id,
  name,
  number_of_rides AS number_of_rides_starting_at_station
FROM (
    SELECT
      start_station_id,
      COUNT(*) number_of_rides
    FROM bigquery-public-data.new_york.citibike_trips AS trips
    GROUP BY start_station_id
    ) AS station_num_trips
INNER JOIN bigquery-public-data.new_york.citibike_stations
ON station_id = start_station_id
ORDER BY number_of_rides DESC 

I keep getting

No matching signature for operator = for argument types: STRING, INT64. Supported signature: ANY = ANY at [18:54] in Big Query

I tried CAST to change the station_id to a string but it already is a string.

What am I doing wrong?

Answer 1

Looks like one of your columns is a string. BigQuery cannot proactively cast to the most probable types and compare values. You have to distinctively type-cast the values in your query:

SELECT
  SAFE_CAST(station_id as INT64) as station_id,
  name,
  number_of_rides AS number_of_rides_starting_at_station
FROM (
    SELECT
      start_station_id,
      COUNT(*) number_of_rides
    FROM bigquery-public-data.new_york.citibike_trips AS trips
    GROUP BY start_station_id
    ) AS station_num_trips
INNER JOIN bigquery-public-data.new_york.citibike_stations
ON SAFE_CAST(station_id AS INT64) = SAFE_CAST(start_station_id AS INT64)
ORDER BY number_of_rides DESC