How to perform a function in SQL using a value from a subquery

Question

I am working with citibike info from bigquery, and am currently learning about subqueries. I am averaging the number of bikes available for each station, and then trying to subtract the number available at every station from the average (to get the difference).

My code looks as follows:

SELECT 
    name,  
    station_id,  
    num_bikes_available,  
    (num_bikes_available) - (AvailableAverage) AS Difference,  
    (SELECT AVG(num_bikes_available) 
     FROM `bigquery-public-data.new_york.citibike_stations`) AS AvailableAverage,
FROM 
    `bigquery-public-data.new_york.citibike_stations` 
ORDER BY 
    num_bikes_available DESC;

Before, my query looked like this:

SELECT 
    name,
    station_id,
    num_bikes_available,
    (SELECT AVG(num_bikes_available) 
     FROM `bigquery-public-data.new_york.citibike_stations`) AS AvailableAverage,
FROM 
    `bigquery-public-data.new_york.citibike_stations`
ORDER BY 
    num_bikes_available DESC;

and it ran fine.

Now it's saying it doesn't recognize the alias AvailableAverage and I don't know why.

I tried ordering the query so the subquery came before the difference line, and it still wouldn't recognize it (it doesn't seem like order is important here).

Do I not understand subqueries? Or is it the aliasing part?

Answer 1

See my comments for 'Why'

Meantime, below is quick, simple "fix"

SELECT 
    name,  
    station_id,  
    num_bikes_available,  
    num_bikes_available - AvailableAverage AS Difference,  
    AvailableAverage,
FROM `bigquery-public-data.new_york.citibike_stations` ,
(SELECT AVG(num_bikes_available)  AS AvailableAverage
     FROM `bigquery-public-data.new_york.citibike_stations`)
ORDER BY num_bikes_available DESC;