Example of a subquery in SQL (within FORM statement)

 There is a public dataset for New York citibike that I have used for this example.

Dataset

This dataset has 2 tables- One is named as citibike_stations and the other is named as citibike_trips

Our requirement is to get data for number of rides starting at each station so we know which is the busiest station for bike business and accordingly bike availability can be maintained.

We have something like this in mind for end result.

Station_id is a common column in both table but only citibike_trips has start_station_id column.

So here is the general idea for querying...

From citibike_trips table, get start_station_id and using COUNT function, get the number of trips for them.

Something like this...

SELECT 

    start_station_id, 

    start_station_name,

    COUNT(*) AS number_of_rides_starting_at_station

FROM 

    citibike_trips

GROUP BY start_station_id

ORDER BY number_of_rides_starting_at_station DESC

This query can also give us the result we desire.

BUT...

There are some stations mentioned on citibike_trips that are NOT

on citibike_stations and vice versa...

And, we want to take data for all stations that are common to both tables.

So to ensure we get data for all stations, we will do inner join of both tables.

Which means that the end result will have data for all stations common

in both tables.

SELECT 

station_id, 

name,

number_of_rides_starting_at_station 

FROM 

(SELECT start_station_id, COUNT(*) AS number_of_rides_starting_at_station

FROM 

citibike_trips

GROUP BY start_station_id) AS station_num_trips

INNER JOIN 

citibike_stations

ON 

station_id = start_station_id

ORDER BY 

number_of_rides_starting_at_station  DESC 

As you can see, our original query is now put within the brackets as

sub-query and given it an alias station_num_trips

And joined both tables using station_id column.

This is an example of sub-query within FROM statement.