7. Combining aggregates II--- What's wrong with my code


#1



Replace this line with your code.


#2

I just had the same problem. It seems to be because both sets of data (elevation and the count of elevation) are both integers so you need to change them to be either floats or decimals. This can be done using the cast operator like this:

SELECT state, 100.0 * ( CAST ( SUM ( CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END ) AS float ) / CAST ( COUNT (*) AS float ) ) as percentage_high_elevation_airports
FROM airports 
GROUP BY state;

I hope this helps :slight_smile: I found the information here if you want anymore help


#3

Thank you ! It worked!


#4

This works, but how can this be the answer they wanted if they haven't discussed "cast" or "float" yet? I still don't understand how it works or what answer I was supposed to give...


#5

@domste10

please help me with the code. why was it not working with the integers. I do not see any problem with (Integer)/(Integer). why do we have to cast it as a floating value. I do not understand why the code written by @microsolver95620 is not working.


#6

@betaslayer52300

It is a feature of many programming languages that whenever an integer is divided by an integer it produces an integer as the answer. For example, 7 / 2 = 3. However when you make at least one of the numbers a decimal or float number, it will produce a decimal or float number. For example, 7.0 / 2 = 3.5.

In the case of this question, because SUM ( CASE WHEN elevation >= 2000 THEN 1 ELSE null END ) and COUNT (elevation) are both integers, when you divide one by the other you will also get an integer answer. This will either be 1 (if both numbers are the same) or 0 (as the count will be greater than the conditional sum). When this is multiplied by 100.0, the answer will therefore either be 100.0 (100.0 * 1) or 0.0 (100.0 * 0). This is why we have to change the data type of one of the initial pieces of data. I hope this all makes sense!


How do brackets affect this code?
#7

@mtzgrs

I've just been playing around with my answer and I think I've found another (easier) solution that doesn't use any cast functions!

SELECT state, 100.0 * SUM ( CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END ) / COUNT (*) as percentage_high_elevation_airports
FROM airports 
GROUP BY state;

I've changed the use of brackets so instead of the integer - integer division being performed first, the 100.0 is now multiplied by the SUM function first, producing a decimal number. This means that the COUNT function is now dividing a decimal number, producing a decimal answer! (I hope that all makes sense, I'm not very good at explaining things)


#8

Makes sense. THANKS!


#9

I got inspired by your answer @domste10. Alternatively, we could also change the number formate in the CASE statement like ' THEN 1.0 ELSE 0 ' . I tried, it works!!
Thank you so much!


#10

Hi! Thanks for sharing. But why write Cast(sum( case when XXX) rather than cast count(case when XXX) in your code?


#11

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.