Many large query tables

Question

I am trying to execute a query from multiple tables in Google BigQuery. I get an error "Field" w.max_temperature "not found in the table" weather.stations ".

Here is my SQL:

SELECT w.station_number, s.LAT, s.LON, w.year, w.month, avg(w.mean_temp) as [mean temp], max(w.max_temperature) as [max temp], min(w.min_temperature) as [min temp], avg(w.mean_visibility) as [avg visbility] FROM [weather.stations] s, [publicdata:samples.gsod] w WHERE w.station_number=s.USAF AND w.wban_number=s.WBAN GROUP BY w.month, w.year, w.station_number, s.LAT, s.LON;

I tried to refer to "max_temperature" on [publicdata: samples.gsod], but for some reason it refers to [weather.stations]. Any ideas? Thank!

Answer 1

It sounds like you're trying to make a JOIN, but in BigQuery SQL, the comma-separated list of tables stands for UNION ALL instead of JOIN (yes, this is strange: yes, we will probably change it if we can, but it might be too late at that point).

What you most likely want:

SELECT w.station_number, s.LAT, s.LON, w.year, w.month, 
       avg(w.mean_temp) as [mean temp], max(w.max_temperature) as [max temp], 
       min(w.min_temperature) as [min temp], 
       avg(w.mean_visibility) as [avg visbility]
FROM [weather.stations] s, 
JOIN [publicdata:samples.gsod] w
ON w.station_number=s.USAF AND w.wban_number=s.WBAN 
GROUP BY w.month, w.year, w.station_number, s.LAT, s.LON;