Why does COUNT count the zero branch of a condition?

Prepare the report input

Original report counts records with needs_review=1. COUNT(expression) counts non-NULL expression values, including zero. The ELSE 0 branch therefore counts records that do not match. Omitting ELSE makes those branches NULL.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE checks(id TEXT PRIMARY KEY,needs_review INTEGER); INSERT INTO checks VALUES('A',1),('B',0),('C',0);

Compare misleading and corrected results

SELECT COUNT(CASE WHEN needs_review=1 THEN 1 ELSE 0 END) AS review_count FROM checks;

SELECT COUNT(CASE WHEN needs_review=1 THEN 1 END) AS review_count FROM checks;

Three records exist but only A is marked 1. The misleading count is three; the correction is one. The nonmatching branch must be NULL for COUNT to omit it.

Check a fresh boundary case

Add another explicit review record D. The count rises from one to two.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO checks VALUES('D',1);
SELECT COUNT(CASE WHEN needs_review=1 THEN 1 END) AS review_count FROM checks;