Why my "overall_avg" alias is not working?

Question

Someone can help me understand why this is not working? Query is not recognizing my alias "overall_avg"... Probably is a primary mistake since I started to work with sequel recently. Thanks!

SELECT
  start_station_id,
  AVG(tripduration) AS duration_per_station,
  overall_avg - duration_per_station AS difference,
  (SELECT 
    AVG(tripduration) 
  FROM bigquery-public-data.new_york_citibike.citibike_trips) AS overall_avg,
  overall_avg - duration_per_station AS difference,
FROM bigquery-public-data.new_york_citibike.citibike_trips
GROUP BY start_station_id

I am trying to pull the start_station_id, average of tripduration for each station, Average of all trips and the difference between average of trip duration for each station and average of all trips. But the code is not recognizing "overall_avg" to find the difference between average of trip duration for each station and average of all trips.

Answer 1

In the SELECT statement, you can only access names from the table in the FROM clause. However, you defined overall_avg as well as duration_per_station in the select statement and at the same time you want to use them in other rows within SELECT. That is not possible, because they are not known in this environment.

I suggest to separate the calculation of overall_avg into a CTE, and then reference it.

WITH
  OverallAVG as (
    SELECT
      AVG(tripduration) as overall_avg
    FROM
      bigquery-public-data.new_york_citibike.citibike_trips
  )

  SELECT
    start_station_id,
    AVG(tripduration) AS duration_per_station,  
    any_value(OverallAVG.overall_avg) - AVG(tripduration) AS difference,
  FROM
    bigquery-public-data.new_york_citibike.citibike_trips, OverallAVG
  GROUP BY
    start_station_id