Ind the percentage of high elevation airports (elevation >= 2000) by state from the airports table

[teraace70818]

Continuing the discussion from Use of "COUNT (column_name)" vs "COUNT(*)":

Use of "COUNT (column_name)" vs "COUNT(*)"

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

<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?

<do not remove the three backticks above>

[kane_zch]

i meet the same question too

and i think the correct answer is:

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

but i still can not get passed.

does anyone konw why…

[teraace70818]

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.

[teraace70818]

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.

Can someone explain whats happens here?

[tn23]

Try adding “ORDER BY state;” to the end. I was having a problem with a previous exercise and that seemed to fix it.

[rudikk99]

there is a NULL value in state, so i removed it

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;

[netsolver72140]

@teraace70818 you should put “100.0 * SUM” instead of “100 * SUM”, I made the same mistake

[kane_zch]

u are right, I changed my answer to

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

and that works! thanks!

but it is seems like a bug…