179-8 SQL Exercise

Hello everyone,
I have been solving a SQL challenge in mission 179, page 8 link.
The questions ask for the population of countries that have urban_pct > 0.5. Before checking the answer, I came up with this solution, which works well:

SELECT f.name country, sum(c.population) urban_pop, f.population total_pop, CAST(sum(c.population) as float) / cast(f.population as float) urban_pct
FROM facts f INNER JOIN (select * from cities) c ON f.id=c.facts_id
GROUP by country
ORDER by 4
limit 50 offset 192;

Instead of using limit and offset, how I can use the WHERE statement to indicate urban_pct > 0.5. Using WHERE urban_pct > 0.5 instead of limit/offset in the above query giving me an error.
I checked the answer and it uses a different approach. I know my approach will work but I am missing something in my solution.
Thanks!

Hey there @saidakbarp!

First, recall the following distinction between WHERE AND HAVING:

  1. ‘WHERE’ clause is used to filter individual rows, before grouping/aggregation has taken place.

  2. ‘HAVING’ is used to filter groups, after grouping/aggregation has taken place.

You can’t use WHERE urban_pct > 0.5, because urban_pct is a direct derivative of aggregated fields. Which is to say that you aren’t trying to filter out individual rows from the queried table anymore - you’re actually trying to filter out grouped fields.

The aggregated field causing the issue here is sum(c.population). You can verify this by removing the sum aggregation method both times it occurs, and then seeing that the WHERE clause will work after that.

Fixing your issue is therefore straightforward enough - since all you needed to use was the HAVING clause instead. Changing the last couple lines of your code like so should do it:

SELECT f.name country, sum(c.population) urban_pop, f.population total_pop, CAST(sum(c.population) as float) / cast(f.population as float) urban_pct
FROM facts f INNER JOIN (select * from cities) c ON f.id=c.facts_id
GROUP by country
HAVING urban_pct > 0.5
ORDER by 4;

On a related note, do you notice how the DQ solution instead uses a different approach with a sub-query, and then a WHERE clause? Why do you think the WHERE clause works in that case? (Hint: Think back to the distinction between WHERE and HAVING I highlighted above!)

2 Likes