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

#1

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



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?

#2

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

#3

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.

#4

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?

#5

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

#6

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;

#7

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

#8

u are right, I changed my answer to

SELECT state,
100.0*COUNT(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...

#9

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