Why do I need a nested query at all?

Hi, I don’t understand why we use a non correlated subquery to calculate the average distance flown per day each month.

In 2003-10, Monday it says the average distance flown is 4301, however if we look at all flights flown in 2003-10 on a Monday, it says the distances flown are: 3711 and 590. Therefore isn’t the average distance flown on a Monday (3711+590)/2 = 2150.5? 4301 appears to be the total distance flown?

I used the following query to work out the averages instead:

SELECT dep_month, dep_day_of_week, sum(distance) / count(id) AS average_distance
FROM flights
GROUP BY 1,2
ORDER BY 1,2;

Is this query right or am I completely misunderstanding the question?

1 Like

Hey. I have the same question. Why need nested queries when you can simply do this:

SELECT dep_month, 
    dep_day_of_week, 
    AVG(distance) AS average_distance
FROM flights
GROUP BY 1,2
ORDER BY 1,2;

Can anybody explain if i missunderstood the exercise or why the subquerry is neccessary in any way?
Thanks in advance & greetings
Tim

1 Like

Hi Tim, I think I’ve figured it out what we got wrong :slight_smile:. The dataset isn’t large enough to see it but there is a slight difference in our queries.

The question was “Find the average total distance flown each day of every month”. Our query only calculates the average distance flown on each day and not the average of the total distance flown!

Take the following example dataset:

Month Day of Week Date Distance
Feb Saturday 2019-02-23 100
Feb Saturday 2019-02-23 500
Feb Saturday 2019-02-23 1000
Feb Saturday 2019-02-16 100

Our query averages all of the Saturdays together, 100,500,1000,100 = 425.

The nested query first adds the total of each date together 100,500,1000 = 1600 then averages the total of each date 1600,100 = 850.

On average, a single flight travels 425km on a Saturday.
vs
On average, the total distance flown across all flights is 850km each Saturday.

Hope that helps!

4 Likes

Hi Paul.

That really helped. Thank you very much. Maybe I was too tired to understand the exercise correctly… :wink: :smiley: