Why is my code wrong? urban_pct problem

Screen Link:
https://app.dataquest.io/m/179/joining-data-in-sql/8/challenge-complex-query-with-joins-and-subqueries

My Code:

select 
         f.name country, 
         c.population urban_pop, 
         f.population total_pop, 
         (c.population/CAST(f.population AS FLOAT)) urban_pct
from     facts f 
inner join 
           cities c on f.id = c.facts_id
group by country
having urban_pct > 0.5
order by 4 asc;

What I expected to happen:
The error upon submitting says “has fewer rows than expected”. I found out that my code eliminates the countries from facts table with more than one cities in cities table.
I did this without using subquery. Can anyone please explain why I am getting only those rows where there is only single city for that particular country?

What actually happened:

The error upon submitting says "has fewer rows than expected"

Hi @bhumikagupta100366, I think I may have found your problem: c.population does not equal urban_pop…it equals the population of one particular city for a particular country. When you perform a GROUP BY on country, there are multiple cities for that country but you aren’t using an aggregate function on the c.population column and therefore the values of urban_pop are not correct.

I tried playing around with your code and found I could produce the desired output with a couple well placed SUM functions.

Let me know if this isn’t enough of a hint and we can try something else!