Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
22. Subqueries in the SELECT clause

Instruction

Awesome! Subqueries can also be used within the column list in a SELECT clause. Here, it's important that the subquery return exactly one row and column.

Take a look:

SELECT
  Name,
  (
    SELECT
      COUNT(*)
    FROM Trip
    WHERE Trip.CityID = City.ID
  ) AS TripCount
FROM City;

In the above query, we provide the name of each city together with the number of trips to that city. Notice that we use the COUNT() function to count the number of trips for each city.

Exercise

Show each mountain name together with the number of hiking trips to that mountain (name the column COUNT).

Stuck? Here's a hint!

Type:

SELECT
  Mountain.Name,
  (
    SELECT
      COUNT(*)
    FROM HikingTrip
    WHERE HikingTrip.MountainID = Mountain.ID
  ) AS Count
FROM Mountain;