Count(case when)


#1

Did anyone catch that the top record has a blank state? I bet your boss would. I once did an insurance report (this was before SQL really caught on in the 80's) that was showing unallocated money. We decided to figure out who's money it was and refund it. It was kind of a big deal because it was about half a million over 30 people. I think one of them got so much back they sent a letter thanking us for refunding it. So don't always ignore orphaned amounts. But in this case we probably don't want empty records showing up on this report. For extra credit, work out how to remove this. Answer below but don't peek :slight_smile:

SELECT state,
COUNT(CASE WHEN elevation <1000
THEN 1
ELSE NULL END
)
AS count_low_elevation_airports
FROM airports WHERE state IS NOT NULL
GROUP BY state;


#2

Why have we used 'THEN 1' in the CASE WHEN statement ?


#3