10

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?

  • 4
    The second example should use SUM instead of COUNT in order to be correct. – Olivier Jacot-Descombes Dec 14 '15 at 21:40
22

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

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.