Having trouble understanding the CASE WHEN statement in SQL

Question

SELECT    state, 
    COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;

In the above statement, what is THEN 1 and what does “1” mean? How does this "1" after THEN affect the output?

Answer 1

First, note that these three expressions are equivalent:

CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END
IF(elevation >= 2000, 1, NULL)
((elevation >= 2000) OR NULL)

If elevation> = 2000, the expression evaluates to "1", otherwise the expression evaluates to NULL.

"1" is usually used as a boolean true, and you can replace the MySQL literal TRUEin the above expressions with equivalent results ... but that's not what "1" means here.

COUNT() 1 , NULL.

, - - COUNT() . .

? NULL . , , COUNT(expr) , expr .

, , elevation = > 2000, , COUNT() a NULL , ... , .

(GROUP BY) - NULL, , .

, , , : AVG(). 3 ... 5, NULL 10... ? 7.5, : (5 + 10) ÷ 2 = 5, 3 . NULL 0, (5 + 0 + 10) ÷ 3 = 5, .

, .

"1" THEN ?

. COUNT(CASE WHEN elevation >= 2000 THEN 'cat videos are funny' ELSE NULL END), , 1, 'cat videos are funny' null, - , null - .

COUNT(elevation >= 2000), , 0 (false) , < 2000 , .

: " COUNT(*) ... WHERE elevation >= 2000?"? . , GROUP BY state , WHERE, , , . , .

---

, ((elevation >= 2000) OR NULL), , . , . , , elevation >= 2000 OR NULL. , elevation >= 2000 1, true, 0, false, NULL, . OR, : 1 OR NULL => 1... 0 OR NULL => NULL... NULL OR NULL => NULL... SQL COUNT(elevation >= 2000 OR NULL) .

Answer 2

Query 1, elevation - > = 2000, NULL ( full for boolean, NULL 0), count_high_elevation_airports.