Non Correlated Subqueries III


#1



i figure out two ways to pass this exercise. i want to know which one is correct?



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

or

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


#2

You actually don't need to have the round() part in it, you can simply use the AVG() as average_distance. and it the grouping part would determine on what columns you are wanting ordered. which is what the numbers represent (column 1, column 2, etc.). So, no matter what is in that column, it will get ordered.


#3

yeah i understand. but it's not about the round.
in the exercise it's mentioned to find the average total distance flown by day of week and month.
so i am asking about how ordering will be done using GROUP BY clause. Whether by column 1,2 or 1,2,3 ?


#5

Hi, if you include two variables/columns in select clause, use 1,2 like your first way (1 for dep_month, 2 for dep_day_of_week); if select three, then use 1,2,3 like your second way (1 for dep_month, 2 for dep_day_of_week, 3 for distance). In my opinion, both of your solutions are correct because 'month' and 'day of week' should already suffice to identify the day for sum. Also, it doesn't matter whether you include a third column ("distance" in your solution or "dep_date" in the example) and group by 1,2,3.


#6

thanks for the clarification


#7

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