Learn

Subqueries

The non-correlated subquery examples we've used so far utilized two tables (flights and airports), but we can also perform transformations on a single table. For instance, sometimes we need to aggregate in multiple steps - like taking an average of a count.

Imagine you’d like to know how many flights there are on average, for all Fridays in a given month from the flights table. First, we’d need to calculate the number of flights per day, and then we’d need to calculate the average based on the daily flight count for each day of the week. We can do this all in one step using a subquery:

```
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;
```

The inner query provides the count of flights by day, and the outer query uses the inner query’s result set to compute the average by day of week of a given month.

Community Forums

Get help and ask questions in the Codecademy Forums

Report a Bug

If you see a bug or any other issue with this page, please report it here.

Learn

Community Forums

Report a Bug

Learn

Community Forums

Report a Bug