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

<Below this line, add a link to the EXACT exercise that you are stuck at.>

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

```

Replace this line with your code.

``<do not remove the three backticks above>``

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 I found the information here if you want anymore help

7 Likes

Thank you ！ It worked!

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…

@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.

@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!

5 Likes

@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)

7 Likes

Makes sense. THANKS!

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!

2 Likes

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

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