179-8 My code produces 'the value for result doesn't look right' when submitted, even though the result is as expected

Hello, I have problem with the challenge in mission 179: joining data in sql.

Here is my code:

SELECT facts.name as 'country', table2.urban_pop, facts.population as 'total_pop', ROUND((CAST(urban_pop as FLOAT)/population), 6) as 'urban_pct'
FROM facts
INNER JOIN (SELECT facts_id, SUM(cities.population) as
                   'urban_pop'
                   FROM cities
                   INNER JOIN facts ON facts.id = cities.facts_id
                   GROUP BY facts.id
            ) as table2 ON table2.facts_id = facts.id
WHERE urban_pct > 0.5
ORDER BY urban_pct ASC;

I run this code and it gives the expected result, down to the six digit value of urban_pct. However, when I click submit, the message ‘the value for result doesn’t look right’ pops up. I cannot find what’s wrong with the result of my code. It is not accepted by DQ.

Please help review my code. Where did I make mistakes?
Thank you very much!

Hi feliciaangelinas

Welcome to the community!
I tried to run your code, but didn’t get any output.
What is the use of this inner join?

There is no need to round urban_pct.

Hi, thanks for your reply. It’s odd, maybe there’s a bug? I run the same code once again in the mission, and it’s still returning similar result:

What is the use of this inner join?

My bad. I just realized this. Initially I was trying to join the tables based on country name, but then noticed I didn’t have to do that as the tables could be joined by country id alone. When updating my code, I forgot that facts_id is in fact, contained in cities table too. Hence the confusing vestigial inner join. Thanks for noticing this!

I have updated my code:

SELECT facts.name as 'country', table2.urban_pop, facts.population as 'total_pop', ROUND((CAST(urban_pop as FLOAT)/population), 6) as 'urban_pct'
FROM facts
INNER JOIN (SELECT facts_id, SUM(cities.population) as
                   'urban_pop'
                   FROM cities
                   GROUP BY facts_id
            ) as table2 ON table2.facts_id = facts.id
WHERE urban_pct > 0.5
ORDER BY urban_pct ASC;

Other than the aliases, I think this has similar structure to the solution provided. It also produces the same expected result in my mission page, however, the message ‘value does not look right’ still appears when I submit this code.
Initially I add the round() function because I thought the result didn’t pass due to rounding, but it doesn’t make any difference.

Thanks!

Okay, that’s fine. As per the Learn section,

For expected results, we rounded to six decimal places; however, when running your query, don’t worry about rounding the urban_pct column.

Hence, you’ll have to get rid of the ROUND function, so the results will be the same as the expected one.
Please check and let know.
Thanks.

Hence, you’ll have to get rid of the ROUND function, so the results will be the same as the expected one.
Please check and let know.
Thanks.

Yes, the code worked after I removed the ROUND function. Thank you very much!

1 Like