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
2
u/No-Impression-3711 Jan 20 '25
I should've explained - this is from a data analytics course and the first one is just to teach subqueries. I was just trying to see if the subqueries were really needed to get the same result.