Counting NULL values in a case statement

Question

I have the following SQL Query:

SELECT jobs.ID,  jobs.title,
SUM(CASE WHEN jobresponses.result = 'true' THEN 1 ELSE 0 END) as True,
SUM(CASE WHEN jobresponses.result = 'false' THEN 1 ELSE 0 END) as False,
SUM(CASE WHEN jobresponses.result != 'true' AND jobresponses.result != 'false' THEN 1 ELSE 0 END) as Incomplete
FROM jobresponses 
JOIN jobs on jobresponses.jobId = jobs.ID
WHERE jobs.ID = 1
GROUP BY jobs.ID, jobs.title

The third case expressions is in practice counting values with a result of NULL, but to be safe (between '', undefined and NULL) I wanted to basically have a catch all "other" type field. However, the issue is that the NULL values aren't being counted. See this SQL Fiddle.

Possible duplicate of [Not equal <> != operator on NULL](https://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — Tab Alleman, Aug 18 '17 at 14:01

Lukasz Szozda · Accepted Answer · 2017-08-18T13:42:27.370

Use IS NULL:

SELECT jobs.ID,  jobs.title,
SUM(CASE WHEN jobresponses.result = 'true' THEN 1 ELSE 0 END) as True,
SUM(CASE WHEN jobresponses.result = 'false' THEN 1 ELSE 0 END) as False,
SUM(CASE WHEN jobresponses.result IS NULL                      -- detect NULL
              OR jobresponses.result NOT IN ('true', 'false')  -- other values
   THEN 1 ELSE 0 END) as Incomplete
FROM jobresponses 
JOIN jobs on jobresponses.jobId = jobs.ID
WHERE jobs.ID = 1
GROUP BY jobs.ID, jobs.title;

Rextester Demo

score 1 · Answer 2 · answered Aug 18 '17 at 13:39

NULL = NULL evaluates to NULL, which is neither true nor false. If you want to catch anything that's not 'true' or 'false' then you can use use

SUM(CASE WHEN (jobresponses.result = 'true' OR jobresponses.result = 'false') THEN 0 ELSE 1 END) as Incomplete

score 1 · Answer 3 · answered Aug 18 '17 at 13:48

A simple way of doing this reverses the then/else logic:

SELECT j.ID,  j.title,
       SUM(CASE WHEN jr.result = 'true' THEN 1 ELSE 0 END) as True,
       SUM(CASE WHEN jr.result = 'false' THEN 1 ELSE 0 END) as False,
       SUM(CASE WHEN jr.result IN ('true', 'false') THEN 0 ELSE 1 END) as Incomplete
FROM jobresponses jr JOIN
     jobs j
     ON jr.jobId = j.ID
WHERE j.ID = 1;
GROUP BY j.ID, j.title

score 0 · Answer 4 · answered Aug 18 '17 at 13:39

you can query as below:

SELECT jobs.ID,  jobs.title,
SUM(CASE WHEN jobresponses.result = 'true' THEN 1 ELSE 0 END) as True,
SUM(CASE WHEN jobresponses.result = 'false' THEN 1 ELSE 0 END) as False,
SUM(CASE WHEN jobresponses.result is null or ltrim(rtrim(jobresponses)) = '' THEN 1 ELSE 0 END) as Incomplete
FROM jobresponses 
JOIN jobs on jobresponses.jobId = jobs.ID
WHERE jobs.ID = 1
GROUP BY jobs.ID, jobs.title

score 0 · Answer 5 · answered Aug 18 '17 at 14:02

Don't use ISNULL(). It will work, but it is a T-SQL function. Use COALESCE() instead. It's multi-platform.

SUM(CASE WHEN COALESCE(jobresponses.result,'') = 'true' THEN 1 ELSE 0 END) as True,
SUM(CASE WHEN COALESCE(jobresponses.result,'') = 'false' THEN 1 ELSE 0 END) as False,
SUM(CASE WHEN COALESCE(jobresponses.result,'') NOT IN ('true','false') THEN 1 ELSE 0 END) as Incomplete

Also, are the only valid values for jobresponses.result supposed to be 'true', 'false' or null? If so, you'd be better off making that field a bit/Boolean instead of a varchar(). It would significantly reduce storage requirements and be a lot easier and more efficient to work with.

NOTE: If it was a `bit` datatype, you'd have to have more care on validating your input to the database (which isn't a bad thing anyway), and then you'd only have to worry about 1,0 and null. Then you wouldn't have to worry about weird data like `undefined` or `''` working it's way into your data. — Shawn, Aug 18 '17 at 14:19

Counting NULL values in a case statement

5 Answers5