Confusion about the GROUP BY 1,2,3

#1

Hello community, I understand basic SQL, but subqueries are rather confusing...
I'm unsure what the GROUP BY 1,2,3 does? And why are there two GROUP BY's in the outer query?

SELECT a.dep_month,
       a.dep_day_of_week,
       AVG(a.flight_count) AS average_flights
  FROM (
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3
       ) a
 GROUP BY 1,2
 ORDER BY 1,2;
#2

Hi @mesqueeb, The group by 1,2,3 is a short-hand method of writing group by 1st column selected, 2nd column selected, and the 3rd. In this case, it stands for dep_month, dep_day_of_week, and dep_date.

Even I initially thought there are two group by's :slight_smile: Its actually one group_by and one order by..

But did you understand, why they are selecting and grouping by dep_date in the inner query? I don't understand why they include that.

#3

In the inner query: Can't they just GROUP BY dep_date ??
There's so little information on this.

#4

I believe they group on the inner so that the numbers appear in the right order for the calculations to compute properly. I learned at a meet up that SQL does not guarantee data will be presented in a logical order and that often you have to include code to ensure proper execution.

#5

Hello,
Can you please explain the working of the code? I can't seem to figure it out.

#6