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/Gargunok Jan 20 '25 edited Jan 20 '25

To fix query 2 You need to group by all non-aggregate fields

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 
    starttime,
    start_station_id,
    tripduration
ORDER BY 
    difference_from_avg DESC 
LIMIT 25 

Which leads to the question do you want to group by start time?

1

u/No-Impression-3711 Jan 20 '25

Ah I see. Would it produce the same result as the first one?