Stuck on high elevation

Get Help SQL
1

Hi, thank you or reading:

i am stuck here as well.

SELECT state,
100.00 * (sum (CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*)) as percentage_high_elevation_airports
from airports
group by 1;

i have 2 theories: there are airports outside of USA in the list so the 1st raw is empty >>> i guess i need to ask if the state IS NOT NULL, then it should work…
the other is that the question is % of airports in this state > 2000ft / total # of airports in the country?? here i’m counting # airports in a state that are high to total # of airports in this state…

i am not sure how to fix it

thanx
Alex

2

there is an entry with NULL value in state column. I removed it but still have an error msg

Select state,
100.00 * (sum (CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*)) as percentage_high_elevation_airports
from (select state, elevation from airports
WHERE state IS NOT NULL)
group by 1
order by 1;

3

I don’t think you need to concern yourself with the NULL state. It won’t affect the result.

Try the select line that I wrote in the previous thread that this was split from.

4

Hey,

I use the ’ city’ column instead of ‘state’ because it references the number of airports…and I got it!

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

1 Like
5

It works! thanx @courserockstar85387 :smiley:

6