Sometimes you want to look at an entire result set, but want to implement conditions on certain aggregates.
For instance, maybe you want to identify the total amount of airports as well as the total amount of airports with high elevation in the same result set. We can accomplish this by putting a
CASE WHEN statement in the aggregate.
SELECT state, COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports FROM airports GROUP BY state;
Using the same pattern, write a query to count the number of low elevation airports by state where low elevation is defined as less than 1000 ft.
Be sure to alias the counted airports as