4/7 Subqueries

[galhardo]

Where does this “a” come from? Is that an alias? Isn’t the sintax of alias = “…AS…”? Why “a” hasn’t this?

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;

[mtf]

Non-correlated sub-queries III

select f.dep_month,
       f.dep_day_of_week,
       avg(f.flight_distance) as average_distance
  from (
        select dep_month,
               dep_day_of_week,
               dep_date,
               distance as flight_distance
          from flights
         group by 1,2,3
       ) f
 group by 1,2
 order by 1,2;

In the above the alias of the outer query is average_distance and of the inner query is flight_distance which the outer query accesses through the variable, f to construct the final table,

|dep_month|dep_day_of_week|average_distance|
|---|---|---|
|2000-01|Monday|1302.0|
|2000-01|Saturday|328.0|
|2000-01|Sunday|691.333333333333|
|2000-01|Thursday|1805.0|
|2000-01|Tuesday|1410.0|
|2000-01|Wednesday|280.0|
.
.
.