Questions regarding SUBQUERIES III


I have several questions about this task:
1) why do we need an "a"?
2) why AS is not used here?
3) why do we put "a." in the first SELECT?
4) why do we need GROUP BY in inner query? it defines the way you see the output, isn`t it?

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


This guy has given a very detailed reply (even too detailed maybe):

Then again the code itself looks more complicated than what it could actually be. I get the same results by using:

select dep_month, dep_day_of_week, avg(distance) as average_distance
from flights
group by 1,2
order by 1,2;

I think they chose a question that is not really suited for an inner vs outer query type of excercise...


select r.dep_month,
AVG(r.flight_count) AS average_flights

   from (SELECT dep_month,
           COUNT(*) AS flight_count
      FROM flights          
    where dep_day_of_week ='Friday'
     GROUP BY 1,2,3) r  
     GROUP by 1       


we can choose any name , here i choose r as a temporary table , in the given solution they choose a as a name of this table , but we ask as the AVG of flight per month for every Friday , so we need this condition ( where dep_day_of_week ='Friday')
i hope that i give you some explanation :slight_smile:


im having the same problem


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