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.
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