I used the following two queries for calculating the percentage of airports with elevation>=2000 by states and it seems to give me the same results. Could anyone help to explain what the difference between them is OR is it similar?
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;
SELECT state, 100.0COUNT(CASE WHEN elevation>=2000 THEN 1 ELSE NULL END)/COUNT() AS dummy FROM airports GROUP BY 1;
The only difference is I used NULL for the CASE statement in COUNT whereas I used 0 for SUM.