Combining aggregates II - Calculating Percentages

Community
1

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;

Why does the query above work and the query below not?

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;

2

Computationally they will give the same result, but for one possible factor… integer division.

given INT / INT

SQL may be computing an INT. We can see where this would be a fly in the ointment.

100.0 * SUM() / COUNT(*)

is float division, so returns a float.