<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>
<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.>
```
I think the correct line should looks like
SELECT state, 100 * (SUM(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END))/ COUNT(*) AS percentage_high_elevation_airports
FROM airports
GROUP BY 1;
and I can see correct numbers in output, but it stands on " Find the percentage of high elevation airports by state."
please can someone put a light on it?
SELECT state,
100*COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) / COUNT(*) as percentage_high_elevation_airports
FROM airports
GROUP BY state;
rather than 100 * (count1 / count2),or it will get the result 0
I found the error, but not the way to fix it… COUNT(*) will count number of airports only in same state , and not airports total number. So you will receive output with % of airports exactly on same state, but not over all table. (this happens due to the grouping). I would use local variable to calculate total number of airports but it looks like this is not accepted , so I’ trying to make sub query now.
here is the query that will show percentage of airports with elevation >=200 in state comparing to all airports in database
SELECT state, 100.0 * (SUM(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END))/ (SELECT COUNT (state) From airports) AS percentage_high_elevation_airports
FROM airports
GROUP by 1;
I export all table in XLS and verify numbers, this query returns correct values, Can someone from codeacademy explain why is still stands that “Find the percentage of high elevation airports by state.”
old query shows % of airports in state with elevation >=2000.
SELECT state,
100.0 * (SUM(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END))/ (SELECT COUNT (state) From airports) AS percentage_high_elevation_airports
FROM (select state, elevation from airports
WHERE state IS NOT NULL)
GROUP by 1;
SELECT state,
100.0COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) / COUNT() as percentage_high_elevation_airports
FROM airports
GROUP BY state;