# How are you supposed to complete the 4th question?

i am stuck on this part if anyone wants to help:

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;

``````
1 Like

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?

Get back to us if your still stuck

1 Like

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

1 Like

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

Get back to us if your still stuck

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

1 Like

iâ€™m still stuck. Not sure what to tryâ€¦

2 Likes

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

Hi
have you figured out the problem ?

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;

2 Likes

Refer to solution posted earlier by user: javablaster3820

1 Like

## 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;``````
2 Likes

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?

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;

1 Like

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

1 Like

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

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

1 Like

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â€ť

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

3 Likes

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;