Different solution as an example

On the ‘Joining Data in SQL’ course I got a little lost on the challenge question but worked out a solution. Basically I grouped by ‘f.name AS country’ instead of grouping by c.facts_id

My solution worked and as others are likely to perhaps be on the same though process as me I thought I would just post my alternative result.

 SELECT  f.name AS country,
         SUM(c.population) AS urban_pop,
         f.population AS total_pop,
         (CAST(SUM(c.population) AS float) / CAST(f.population AS float)) AS urban_pct
   FROM  facts AS f
  INNER  JOIN cities as c ON f.id = c.facts_id
  GROUP  BY country
 HAVING  urban_pct > 0.5
  ORDER  BY urban_pct

This may not be a great alternative solution but maybe it helps other students out a bit with their thinking.
OK, onto the next course :star_struck:

4 Likes

I am confused about your approach to the city population. Isn’t that going to give the total population of all the cities in the table?

SUM(c.population) AS urban_pop

No it won’t as sum works against aggregate results by country. In other words firstly it joins facts (countries table) and cities tables. In this combined table you have for one country set multiple cities. Once it is one big table it will Group By. Every time Group By executes it sends set of rows one country at a time (e.g. Australia) to sum to tally up (i.e. every city in Australia (denoted as c.population) will be added up (imagine loop). Once work for Australia is done, SQL Engine will move to the next country etc etc.

Does that make sense?