SELECT list expression references column xxx which is neither grouped nor aggregated at, why need to use subquery?

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.

Answer

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.