So I'm kinda new to SQL and been following some tutorial courses online. I want to compare the num_bikes_available at a station to the average num_bikes_available. My question is why cant it just show the average using the OUTER SELECT clause? Why do it need to be done using SUBQUERY? My Answer. Tutorial Answer.
1 Answers
Query SELECT AVG(num_bikes_available) FROM citibike_stations will return a single row with average calculated over all rows.
Now, if you add station_id like SELECT station_id, AVG(num_bikes_available) FROM citibike_stations, this is also supposed to return a single row, but BigQuery does not know which exactly station_id value do you need. Therefore you see that error.
BigQuery can show average for every distinct station_id value it finds, but to do that it needs the GROUP BY clause like SELECT station_id, AVG(num_bikes_available) FROM citibike_stations GROUP BY station_id.
Related