CODEWITHSUNDEEP
mysqlsqlselectrelational-database
user3047181
user3047181

Reputation:

SQL: Select SUM of population of all towns on the same road

Imagine I have three tables:

Towns
| town_name | population |

Roads
| road_name |

TownsOnRoads
| town_name | road_name |

where towns and roads have an m to n relationship - a town can be on any number of roads, and a road can pass through any number of towns.

What I want to get is a table like so:

RoadPopulations
| road_name | population |

where population is the sum of all the populations of every town on that road. I'm new to SQL so I'm having trouble figuring out how exactly to structure the select query. I've gotten as far as:

SELECT DISTINCT SUM(population) as popn
FROM Towns t JOIN TownsOnRoads tor
ON t.town_name = tor.road_name;

But obviously that just gives me a 1 column table with 1 tuple - with a popn value that has no meaning whatsoever... Any ideas?

EDIT: By the way here's a working query for just one particular road.

oh and if it helps, here's the working query to just get the answer for one particular road: 

SELECT DISTINCT SUM(population) as popn
FROM Towns t JOIN TownsOnRoads tor
ON t.town_name = tor.road_name
WHERE tor.road_name="route 66";

Upvotes: 0

Views: 2910

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You have to join on the town_name column from the tables. Also use group by so you get the total population for each road_name.

SELECT tor.road_name, SUM(population) as popn
FROM Towns t 
JOIN TownsOnRoads tor ON t.town_name = tor.town_name
group by tor.road_name

Upvotes: 2

Related Questions