Why is CAST-ing Necessary And Which Elements To Apply It To?

Q&A DQ Courses
, , , , , ,
#1

Screen Link:

Regarding this answer:

SELECT
    f.name country,
    c.urban_pop,
    f.population total_pop,
    (c.urban_pop / CAST(f.population AS FLOAT)) urban_pct
FROM facts f
INNER JOIN (
            SELECT
                facts_id,
                SUM(population) urban_pop
            FROM cities
            GROUP BY 1
           ) c ON c.facts_id = f.id
WHERE urban_pct > .5
ORDER BY 4 ASC;

…and specifically this part:

  (c.urban_pop / CAST(f.population AS FLOAT)) urban_pct

In general, why is casting to float necessary here?
So c.urban_pop and f.population are INT to begin with correct?
Dividing INT by INT is bad somehow? Why?
In this case, the divisor was casted to float. Does it have to be the divisor or could it be the dividend instead?
When I submitted my answer, I casted both the dividend and the divisor as float but I can see that wasn’t necessary?

Thank you for your time!

#2

Hi @gosaints,

When we try to divide two integers, SQL will round the result to an integer. If for some reasons it’s ok for us, then everything is fine. But in the majority of cases we would prefer to have the result of division not as an integer, but as a float (probably rounded to some decimal, but only if we decide so). For this purpose we use CAST And yes, it doesn’t matter whether to cast into float numerator, denominator, or both, because anyway the division of a float and an interer will be a float anyway.

1 Like
#3

Another solution is to multiply the column by 1.0 without any casting.

2 Likes
#4

Understood! Thank you @Elena_Kosourova and @alvinctk!

2 Likes