Why am I getting an error while running the second code

Question

I am using Google Bigquery to query data from a Public dataset new_york_citibike. I want to get num_bikes available and the average number of bikes available at a station_id. The query is:

SELECT station_id, num_bikes_available (SELECT AVG(num_bikes_available) AS average_bikes_available
FROM bigquery-public-data.new_york_citibike.citibike_stations
)
FROM bigquery-public-data.new_york_citibike.citibike_stations

The second query is:

SELECT station_id, num_bikes_available, AVG(num_bikes_available) AS average_bikes_available
FROM bigquery-public-data.new_york_citibike.citibike_stations
GROUP BY station_id

The error which I got is "SELECT list expression references column num_bikes_available which is neither grouped not aggregated". Why is it so that I have to aggregate the num_bikes_available in the second case.

Answer 1

you need to provide sample data but I think this is what you are trying to do :

SELECT station_id
       , sum(num_bikes_available) as num_bikes_available
       , avg(num_bikes_available) AS average_bikes_available
FROM bigquery-public-data.new_york_citibike.citibike_stations
GROUP BY station_id