How are you supposed to complete the 4th question?


#1

i am stuck on this part if anyone wants to help:
https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii

the code keeps saying this:
Error: near line 2: aggregate functions are not allowed in the GROUP BY clause

select dep_month,
       dep_day_of_week,
       avg(flight_distance) as average_distance
       from (select dep_month, dep_day_of_week, dep_date, count (*) as flight_distance
             from flights
             group by 1,2,3
             ) flight
             group by 1,2
             order by 1,2;

#2

The outer query looks good look again at your inner query

think about what sub-query is doing?

For example are we counting the distances or totaling them up? What other function function apart from "COUNT" can we use?

Also please add little spacing and add comments to your inner and outer queries.

Get back to us if your still stuck


#3

I haven't figured it out yet, but I believe you use SUM instead of COUNT


#4

Thats correct. Now work out how you will code that into the program

Get back to us if your still stuck


#5

Oh I'm sorry I didn't mean to answer a hypothetical question. Yes, I figured it out.


#6

i'm still stuck. Not sure what to try..


#7

I am stuck as well although changed my code again and again ,tried to alter COUNT as SUM ,but still not work


#8

Hi
have you figured out the problem ?


#9

SELECT a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM (
SELECT dep_month,
dep_day_of_week,
dep_date,
SUM(distance) AS flight_distance
FROM flights
GROUP BY 1,2,3
) a
GROUP BY 1,2
ORDER BY 1,2;


#10

Show us your code snippet


#11

Refer to solution posted earlier by user: javablaster3820


#13

Solution

SELECT b.dep_month, 
		 b.dep_day_of_week, 
   AVG(b.flight_distance) AS average_distance
FROM (
SELECT dep_month,
         dep_day_of_week,
				 dep_date,
         SUM(distance)  AS flight_distance
	FROM flights
  GROUP BY 1,2,3
	 ) b  GROUP BY 1,2  ORDER BY 1,2;

#14

Hi, could please explain what does the first GROUP BY 1,2,3 do? I would've GROUP BY dep_month in my inner query, then GROUP BY dep_day_of_week in outer query. Thoughts?


#15
  1. what's the purpose of a.xxxxx in the code?

  2. can you explain the a in the following:
    from (
    SYNTAX) a

  3. where did 1,2,3 come from in the group by statement?

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


#16

Thanks for the advice. I just saw this email and I will mess around with what you said.


#17

I don't get what's the a.xxx meaning and also from ( ) x


#18

Basically, GROUP BY can be stated using two ways:
1- you can mention the columns names by order of priority
2-you can refer to the columns by the numbers (ie: column 1, column 2 ...)

So here our codes are similar since your names and my numbers corresponds except that I grouped by all the columns inside the inner query.

That is needed since you're looking to select the flights by day of the week of the same week of the same month = GROUP BY
1- dep_day_of_week
then
2- dep_date (since you wanna avoid having two fridays of different weeks grouped in the same cell because this will be done in the outer query)

3- dep_month

which can be substituted by GROUP BY 1,2,3

Hope this answers your question :slight_smile:


#19

Hi Leilajoon,

This code is simply used to facilitate the task but they missed to explain the meaning of the syntax:

FROM (
SYNTAX) a

As you know "FROM" syntax tells the program from which table it should refer to the data. but since here we use the same columns names as other tables previously declared, when you call the SELECT SYNTAX in the outer query it is always better to mention (in this case where two tables have same columns names) Table.column_name instead of only column_name .

To do this we need to call the inner query table by a name. That name here is the "a" :grin:

So now we learned how to declare a table name using:

FROM (
SYNTAX) a

I hope that I am not mistaking and that will help understanding the task :relaxed:


#20

Very helpful... if mods see this post, please add a comment about how inner query tables are created and used.


#21

All right, I figured out the answer and it's very, very similar to what MrFisch posted, but instead of COUNTING everything, you want to do this instead: _______. Fill in the blank and think about what other operations you have in store instead of COUNT.

Also, can someone explain why I need the word "flight" after the closing parentheses in the code?


Feeling generous today.
Here's the answer, but I want you to struggle just another 5 minutes before you look. I bet most of you are 95% there already:

SELECT dep_month,
dep_day_of_week,
AVG(flight_distance) as average_distance
FROM(
Select dep_month,
dep_day_of_week,
dep_date,
SUM(distance) as flight_distance
FROM flights
GROUP BY 1,2,3
) flight
GROUP BY 1,2
ORDER BY 1,2;