Is ELSE 0 implied in my COUNT CASE WHEN statement?

Simply enough in the first case you are counting 1s & NULLs. (NULL is returned if none of the conditions in the CASE statement match and there is no ELSE clause.) NULLs don't get counted. In the second case 1's and 0's. 0's can be counted.

Quick example:

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

Output:

enter image description here


If you're not specifying the else part to case statement, it will then return NULL by default, and in your case that is a good thing, because count will count the non-NULL values. If you return anything else from case, it doesn't matter if it's 1, 0 or 2, it will always be counted as 1.

If you would use sum instead of count, then you should return 1 or 0.