Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
21. 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 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;