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.
Show each mountain name together with the number of hiking trips to that mountain (name the column COUNT).
Type:
SELECT
Mountain.Name,
(
SELECT
COUNT(*)
FROM HikingTrip
WHERE HikingTrip.MountainID = Mountain.ID
) AS Count
FROM Mountain;
