human
Schema:
train (id, train_number, name, origin, destination, time, interval)
station (id, network_name, services, local_authority)
route (train_id, station_id)
weekly_weather (station_id, day_of_week, high_temperature, low_temperature, precipitation, wind_speed_mph)
Please answer with SQL.
assistant
Sure, I understand.
human
Can you list the services for each station?
assistant
SELECT services FROM station ;
human
Can you also show the the number of trains for each station?
assistant
SELECT COUNT( * ) , t1.services FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id GROUP BY t2.station_id ;
human
Also include the the station network name.
assistant
SELECT COUNT( * ) , t1.network_name , t1.services FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id GROUP BY t2.station_id ;