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.
COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports
GROUP BY state;