patternsqlMajor

Is ELSE 0 implied in my COUNT CASE WHEN statement?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

caseelsestatementimpliedwhencount

Problem

What's the difference between COUNT(CASE WHEN [Column A] = ____ THEN 1 END
and COUNT(CASE WHEN [Column A] = ____ THEN 1 ELSE 0 END?

I've been using the former and I haven't seen the difference thus far; what is the reason for adding the ELSE 0 - are there situations where SQL Server will incorrectly count?

Solution

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:

Code Snippets

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;

Context

StackExchange Database Administrators Q#123664, answer score: 23

Revisions (0)

No revisions yet.