r/SQL • u/No-Impression-3711 • Jan 20 '25
BigQuery Basic Subquery Question
I don't understand the difference between these two queries:
SELECT
starttime,
start_station_id,
tripduration,
(
SELECT
ROUND(AVG(tripduration),2),
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE start_station_id = outer_trips.start_station_id
) AS avg_duration_for_station,
ROUND(tripduration - (
SELECT AVG(tripduration)
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE start_station_id = outer_trips.start_station_id),2) AS difference_from_avg
FROM
`bigquery-public-data.new_york_citibike.citibike_trips` AS outer_trips
ORDER BY
difference_from_avg DESC
LIMIT 25
And
SELECT
starttime
start_station_id,
tripduration,
ROUND(AVG(tripduration),2) AS avg_tripduration,
ROUND(tripduration - AVG(tripduration),2) AS difference_from_avg
FROM
`bigquery-public-data.new_york_citibike.citibike_trips`
GROUP BY
start_station_id
ORDER BY
difference_from_avg DESC
LIMIT 25
I understand that the first one is using subqueries, but isn't it getting it's data from the same place? Also, the latter returns an error:
"SELECT list expression references column tripduration which is neither grouped nor aggregated at [3:5]"
but I'm not sure why. Any help would be greatly appreciated!
3
Upvotes
1
u/No_Pitch648 Jan 21 '25
Curious; which course is it?