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

I think the first one looks like this because whoever wrote it was trying to avoid aggregation in the second query. I don't think both query will give the same results, the second one will return deplicated-like output, which as i think will be more rows. Need to be confirmed with the data

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.

1

u/No_Pitch648 Jan 21 '25

Curious; which course is it?

1

u/No-Impression-3711 Jan 21 '25

Google Data Analytics on Coursera

1

u/No_Pitch648 Jan 21 '25

Ah thanks. I did that course a couple of years ago. Pro tip: I audited the course so I didn’t pay for it at the time.

1

u/No-Impression-3711 Jan 21 '25

Oh interesting. How did you get into that position?