Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Instruction
Awesome! Subqueries can also be used within the column list in a SELECT clause. Here it's important that the subquery returns exactly one row and column.
Take a look:
SELECT
name,
(
SELECT COUNT(*)
FROM trip
WHERE city_id = city.id) AS trip_count
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 function COUNT() 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
name,
(
SELECT COUNT(*)
FROM hiking_trip
WHERE mountain_id = mountain.id) AS count
FROM mountain;
