179-8 Almost right, but missing some rows from result

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

My query below produces almost the same result as the provided solution, and the calculations appear to be correct, but a couple of rows are missing from my results.

SELECT countries.name AS country,
    SUM(cities.population) AS urban_pop,
    countries.population AS total_pop,
    CAST(cities.population AS FLOAT) / countries.population AS urban_pct
    FROM cities
    INNER JOIN facts AS countries ON cities.facts_id = countries.id
    WHERE urban_pct > 0.5
    GROUP BY countries.id
    ORDER BY urban_pct;

Here are the first few rows of my results:

country urban_pop total_pop urban_pct
Uruguay 1672000 3341893 0.5003152404939356
Brunei 241000 429646 0.5609269026128487
New Caledonia 157000 271615 0.5780240413821034

And here are the first few expected results:

country urban_pop total_pop urban_pct
Uruguay 1672000 3341893 0.5003152404939356
Congo, Republic of the 2445000 4755097 0.5141850944365594
Brunei 241000 429646 0.5609269026128487
New Caledonia 157000 271615 0.5780240413821034

Why is the Congo missing from my version? I know I took a different approach than suggested, but I can’t figure out why the results are different.

The reason is that you got lucky with your implementation.

Countries - Uruguay, Brunei an New Caledonia have only one city. But Congo, Republic of the has more than one.

So, in your code when you have -

The above works for one city because you are not summing the population above. But, when a country has more than one city in the data, the above fails.

That’s why the content/instructions suggest using a subquery. The subquery allows us to get the sum of the population separately.

1 Like