4. Non-Correlated Subqueries


#1

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



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


#2

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.


#3

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;

#4

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?


#5

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:


#6

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


#7

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