Understanding Non-Correlated sub queries

Get Help SQL
#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;

Please explain the following:

  1. Where does the “flight_count” on line 3 come from? I cannot find it in the columns, and so I do not understand what it does.
  2. Why is there an “a” in the code? What does it do?
  3. How does Group By really work? I’ve never fully understood it, the definition is confusing. And in this case, what does “1, 2, 3” represent?
#2

I will explain GROUP BY:

Look at this table

+----+----------+-----+
| id | salery   |name |
+----+----------+-----+
| 1  | 2000     | Dave|
+----+----------+-----+
| 2  | 3000     | John|
+----+----------+-----+
| 3  | 1500     | John|
+----+----------+-----+
| 4  | 2000     | Dave|
+----+----------+-----+
| 5  | 4000     | Doe |

You can see that the table has the following records with duplicate names:

+----+----------+-----+
| id | salery   |name |
+----+----------+-----+
| 1  | 2000     | Dave| <<<< 
+----+----------+-----+
| 2  | 3000     | John| <<
+----+----------+-----+
| 3  | 1500     | John| <<
+----+----------+-----+
| 4  | 2000     | Dave| <<<<
+----+----------+-----+
| 5  | 4000     | Doe |

If you now wanna know the total amount of all customers

You can do it like this for every Name

SELECT SUM(salery) 
    FROM mytable 
       WHERE name = 'Dave';

Result should be 4000 (2000+2000)

For two names that should be a solution. But if you didnt know all names or your Database is much bigger :wink: not very helpful.

For that we have GROUP BY.

SELECT name, SUM(salery)
   FROM mytable 
      GROUP BY name;

This would produce the following result

+----+------------+-----+
| id | SUM(salery)|name |
+----+------------+-----+
| 1  | 4000       | Dave|
+----+------------+-----+
| 2  | 4500       | John|
+----+------------+-----+
| 3  | 4000       | Doe |
+----+------------+-----+

If you didnt type GROUP BY you would get the SUM from all SALERY Records and a Name of your first record

1 Like
#3

The line above is where flight_count is defined. It is the result of COUNT(*).

Below is where a is defined. It is an alias for the resulting table created by that SELECT.

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