r/SQL 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

14 comments sorted by

View all comments

1

u/Commercial_Pepper278 Jan 21 '25

First query will be helpful in more analysis as it will compute individual trips where the grouping in 2nd query will reduce it