Non-Correlated Subqueries III


#1

Code:
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;

Questions:
1. Why is there an "a." infront of the column names in the outer query?
2. GROUP BY "1,2,3" seems to group by the three columns based on the order they were written in the inner query. Is that correct? Same applies to GROUP BY "1,2" in the outer query


#2

Bump

I had the exact same questions.


#3

I'm still pretty fuzzy on this module myself, but my understanding is that the "FROM (SELECT ... ) a" statement defines a table named "a" containing the result of the SELECT query in parentheses. The preceding section operates on this new table a. In this example the inner query is creating a table containing the month, day, date, and flight counts for each unique combination of the other three columns and calling it "a". The outer query then averages the flight count from a for each month and day of week. Why on earth the operations on the alias are written BEFORE the alias is defined is a mystery to me that Code Academy must feel is too obvious to explicitly explain to poor plebs like us.

GROUP 1,2 tells the table to split up the averages by unique combinations of columns 1 and 2, otherwise AVG will just average ALL entries into a single row. ORDER just tells it how to sort the rows: first by month, then by day of week.


#4

@evan-ge

Upon some further digging, I think you are correct. I came across this response to a different thread that covers it very well. See link below:


#6

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