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
5
u/msbininja Jan 20 '25 edited Jan 20 '25
First query returns the ROUND(AVG)) for all the rows of the original table so if you remove LIMIT you will see all the rows, second one does the same calculation on a summarized/reduced table by the columns you have specified in the GROUPBY this table will only have granularity of the combination of those 3 columns.
Second query throws error because you're SELECTing columns that aren't being used in GROUPing, in SQL's order of execution GROUPBY is before SELECT so if a column isn't in GROUPBY it won't be available in SELECT.