4. Non-Correlated Subqueries

What does GROUP BY 1,2,3 do?
Does the order of 1,2,3 matter?

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii?action=resume

3 Likes

Yes!
I’ll answer you with an example.

So, in a query:

select id, location, distance, count(1)
from TABLE
group by 1,2,3;

the reference of the numbers (1,2,3) is exactly what you put in 1 line of query, so,
1 = id
2 = location
3 = distance.

2 Likes

This query:

1. SELECT a.dep_month,
2.        a.dep_day_of_week,
3.        AVG(a.flight_count) AS average_flights
4.   FROM (
5.         SELECT dep_month,
6.               dep_day_of_week,
7.                dep_date,
8.                COUNT(*) AS flight_count
9.           FROM flights
10.          GROUP BY 1,2,3
11.        ) a
12.  GROUP BY 1,2
13.  ORDER BY 1,2;

is the same as

1. SELECT a.dep_month,
2.        a.dep_day_of_week,
3.        AVG(a.flight_count) AS average_flights
4.   FROM (
5.         SELECT dep_month,
6.               dep_day_of_week,
7.                dep_date,
8.                COUNT(*) AS flight_count
9.           FROM flights
10.          GROUP BY dep_month,dep_day_of_week,dep_date
11.        ) a
12.  GROUP BY a.dep_month,a.dep_day_of_week
13.  ORDER BY a.dep_month,a.dep_day_of_week;
5 Likes

Thank you @lineavila for the simple, yet very effective explanation. The previous courses never taught this. Your explanation makes perfect sense to me.

Just one more question if I may. Why the use of a. ? Can we use any letter we want instead of the a. Could we use for example b.dep_month?

1 Like

You’re welcome! :smile:
the a is because we put a alias in clause FROM (line 11 in the last example).
A more simple example is:

SELECT a.dep_month, a.dep_day_of_week
FROM flights a;

So, when we put any char/word in front of the name of the table we are putting a ALIAS for that table.
It is more used when we call two or more tables in the same query.
For example:

SELECT a.id, a.dep_month, b.id, b.distance
FROM flights a, airports b;

So, the table flights has a alias ‘a’ and the table airports has the alias ‘b’. Whe can put any char or word. And when we put a alias followed by the point, followed by the name of the column we are saying that column is from that table. So, in a.id we’re saying ‘it’s the id from the table a (flights)’.

I hope I was helpfull and sorry for my English :slight_smile:

3 Likes

@lineavila Thanks again. You’re english is perfect. :smiley:

2 Likes

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