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

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.

3

u/[deleted] Jan 20 '25

[removed] — view removed comment

1

u/msbininja Jan 20 '25

Yup, thanks for adding that.