Why is there a difference between calling f.population versus c.population

Screen Link:
Using SQL Joins To Combine Data From Two Tables — Finding The Most Populous Capital Cities | Dataquest

My Code:

SELECT c.name as capital_city, f. name as country, f.population
from cities as c
inner join facts as f on f.id= c.facts_id
where c.capital=1
ORDER BY 3 desc
limit 10

But the right code was replacing f.population with c.population
If they are already joined, then wouldn’t it not matter which one I use?

Hi @hliu19922019 ,

Note that the outputs differ when you use f.population instead of c.population.

c.population is the population of the table Cities, which means it’s the population of each city.

f.populationis the population of the table Facts, which means it’s the population of each country.

The instructions ask for the population of the City, not the Country:

You should include the following columns, in order:

  • capital_city, the name of the city.
  • country, the name of the country the city is from.
  • population, the population of the city.

Hope hits helps you.

1 Like

Thank you! That makes sense! Another question for you just so I don’t need to post another separate topic.

For this exercise Using SQL Joins To Combine Data From Two Tables — Challenge: Complex Query With Joins And Subqueries | Dataquest

the correct code is


select f.name as country, c.urban_pop, f.population as total_pop, 
(c.urban_pop/ cast(f.population as float)) as urban_pct
from facts as f
inner join (SELECT
                facts_id,
                SUM(population)as urban_pop
            FROM cities GROUP by facts_id) c on c.facts_id=f.id

where urban_pct>0.5
order by urban_pct asc

why did I need to separate the CAST function to only the f.population?

Why can’t I do CAST(c.urban_pop/ cast(f.population as float) and put the whole division into the CAST function?

You don’t need to have the whole division into the CAST function because the output of the division is already a float, so there’s no need to convert. But if you want to, it would be like this:

CAST((c.urban_pop/ cast(f.population as float)) as float)

But you need to use CAST on f.population because SQL won’t let you divide a float by an int.

1 Like