Non-Correlated Subqueries III


#1
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;

Hi all, please help me to understand.

  • What’s the use for “a.” in: a.dep_month, a.dep_day_of_week, a.flight_count?

  • What’s the use for GROUP BY 1,2,3? There’s no column named 1,2,3

Thank you in advance.


Non-Correlated Sub queries III
#2

•What’s the use for “a.” in: a.dep_month, a.dep_day_of_week, a.flight_count?
It is called an Table-alias normaly they use the =keyword= as
like with

(
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               COUNT(*) AS flight_count
          FROM flights
         GROUP BY 1,2,3
       ) AS a

you created the =virtual= table a

https://stackoverflow.com/questions/7367548/sqlite-table-aliases-effecting-the-performance-of-queries
Aliasing a table will result a reference to the original table that exists for the duration of the SQL statement.

===========================================================
•What’s the use for GROUP BY 1,2,3? There’s no column named 1,2,3

maybe you get the idear by reading
https://dba.stackexchange.com/questions/86609/why-do-we-use-group-by-1-and-group-by-1-2-3-in-sql-query
and PLEASE notice that it is DANGEROUS to use, imagine the column-order in the table being changed in future developments!!


#3

Hi Leonhard,

Will the virtual table “a” use a lot of resources?
Is there any more efficient way to do this?

Thank you.


#4

May be you start by reading
Non-Correlated Subqueries III - A few questions

The performance question depends on the kind of DB you are using and more…
but first you have to learn the SYNTAX, that is what you are doing now…

Here some sites where you could find discussions / opinions

https://stackoverflow.com/questions/6015175/difference-between-view-and-table-in-sql
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1144905363974
https://stackoverflow.com/questions/9025823/nested-select-in-from-clause?noredirect=1&lq=1
https://stackoverflow.com/questions/18502702/how-does-a-sub-query-in-the-from-clause-work
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql


#5

Thank you very much for your guidance, Leon.


#6

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