4. Non-Correlated Sub Queries (EXAMPLE GIVEN)


#1

I've had all kinds of problems with this lesson, more than any other CC lesson taken so far. Not sure if it's the way the question is worded or if it's because there's a lot of code with new concepts that can be overwhelming.

For the example given, I read the question that they want us to find the average number of flights for just Fridays in a given month, but when I cut and paste the example given, it returns for every day of the week from Monday thru Sunday and from there I found myself completely lost.

I've created my own code for what I think might be a better solution? I'm not trying to say my solution is more correct than CC. There is a good chance I may be wrong. I'm simply trying to fully understand the lesson. Please help clarify if my understanding is correct. Thanks in advance for any feedback.

SELECT
	f.dep_month AS 'Month',
  f.dep_day_of_week AS 'Day of Week',
	AVG(f.total) AS 'Avg. Flights'
FROM (
	SELECT
  	dep_month,
    dep_day_of_week,
    dep_date,
		COUNT(dep_day_of_week) as 'total'
  	FROM flights
    WHERE dep_day_of_week = 'Friday'
    GROUP BY 1) f
 GROUP BY 1;


https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii?action=lesson_resume


#2

hey. I have a question. what is group by 1?? I know what the "group by" is. but I can't get the 1, 2, 3 !!!! shouldn't we type down a column's name instead?


#3

In the outer query SQL keeps an index of what your are selecting. So 1 is Month. It's basically the same as saying GROUP BY f.dep_month.

If you wanted to instead group by Day of Week you could instead type GROUP BY 2 which is also the same as saying GROUP BY f.dep_day_of_week.

Hope this helps. I'm pretty new to SQL so if anyone can clarify my info provided is correct it would be appreciated :slight_smile:


#4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.