FAQ: Subqueries - Non-Correlated Subqueries III


This community-built FAQ covers the “Non-Correlated Subqueries III” exercise from the lesson “Subqueries”.

Paths and Courses
This exercise can be found in the following Codecademy content:

SQL: Table Transformation

FAQs on the exercise Non-Correlated Subqueries III

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!


Hi, I don’t understand the meaning of ‘a.’ in the query :blush:

why I have to add this letter to complete the exercize?

Non-Correlated Subqueries III

SELECT a.dep_month,
AVG(a.flight_distance) AS average_distance
SELECT dep_month,
sum(distance) AS flight_distance
FROM flights
GROUP BY 1,2,3
) a


Please excuse all the edits (I’m a SQL newb) and I could be wrong but I believe the inner query…

SELECT dep_month,
SUM(distance) AS flight_distance
FROM flights
GROUP BY 1,2,3

…is being set to a variable of “a”. In turn, the outer queries runs a query on the “a” variable or table.

The inner query is used to create an organized table sorted by month, day of the week, and then I believe for cosmetic reasons(?), month date. The ‘flight_distance’ is a sum of all miles flown across all flights for that day in the month.

The outer query queries this cleaned up and organized temporary table ‘a’ and groups the final output by departure month, then all of the Mondays, Tuesdays, Wednesday’s, etc. for that month and then the average flight distances for each of those sets of days of the week.

In the end, we have the average total distance flown by day of week in each month.

To understand what was happening, it was helpful to run the inner query in the example first and see the table results and then add the outer query around it and again, see the results.


Thanks for this. Sheesh, huge jump from Non-Correlated Subqueries 2.


Can someone explain why the ‘a’ is there? I don’t understand why we need it? And can someone explain what the ‘a’ does?

1 Like

Neutechnica, thanks for the info. I’m also a SQL newb to this, and struggled to understand this setup until I started looking through forums.

There doesn’t seem to be enough lessons covering subqueries or nested queries (though, please let me know if there’s a section I’m overlooking).

danyal1414 - The “a” is being used as the name of the copied table version of “flights”. In the outer query you have:
SELECT a.dep_month,
AVG(a.flight_count) AS average_flights

The “a” is the name of the table and after the period (.) is the name of the column


What I’m still wondering is what is the difference between adding that “a” and using “as xxxx” to, I suppose, alias the entire query. So instead of

SELECT a.dep_month,
AVG(a.flight_distance) AS average_distance
FROM ( … ) a

it would be

SELECT inner_query.dep_month,
AVG(inner_query.flight_distance) AS average_distance
FROM ( … ) as inner_query

Is it just shorthand? Is the “as” superfluous in this case? It seems to work either way. Maybe I missed it, but I feel like this “a” notation was kind of sprung on us in the lesson and not well explained.

1 Like

I agree this was a pretty huge jump. I think this part of the lesson needs to be revised. Unless I am mistaken I don’t think setting variables has ever been discussed in any of the previous SQL lessons (excuse me if I am wrong or have forgotten). I also did not understand the “a.” I only understood this once it was explained here in the forum. I could only recognize this as setting a variable or “temporary table” from prior knowledge of this from programming languages that would set variables.

I was also stumped in how the outter query was able to AVG(a.flight_distance) being that the flight table did not have a “flight_distance” only “distance”. I later noticed it was set AS flight_distance in the subquery. It seems as if everything is set first in the subquery then calculated above in the outter query. Just like when we set the variable “a”.

I think it would be awesome if there could be links to previous lessons that pertain to what is being taught in the advanced lessons. It would help solidify what we have already learned if it has been forgotten and lead us to faster eureka! moments. :slightly_smiling_face:


I agree. The FROM( … ) a felt totally sprung on us. I had the same thought that we could have temporarily named this AS inner_query. I only understood this notion as setting it to a temporary table because I have some minor experience programming and in programming we sometimes set variables or “temporary” numbers to a letter. In this case we set a temporary table to a letter.


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

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, 
    AVG(distance) AS average_distance
FROM flights

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


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.
On average, the total distance flown across all flights is 850km each Saturday.

Hope that helps!

1 Like

Hi Paul.

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


For the solution, the inner query has “Sum(distance)”. However, to find the average distance travelled for a single day, I think using “Sum(distance)” is a mistake because then the outer query will only have 1 distance to average, which is the total distance travelled that day. Shouldn’t we take out the “sum” and simply puts “distance”–in this way we can average the distances in the outer query?