CODEWITHSUNDEEP
sqlsql-servert-sql
George Edwards
George Edwards

Reputation: 9229

Counting NULL values in a case statement

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.

Upvotes: 0

Views: 7092

Answers (5)

Shawn
Shawn

Reputation: 4786

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

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

Upvotes: 6

D Stanley
D Stanley

Reputation: 152566

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

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

Upvotes: 0

Related Questions