question about "not in"

Question

question about "not in"

Kai Yang 166

Hello List,
I submitted the code below:

select
(select count(*) from gemd.Respondents_tidy) - SUM (CASE WHEN gender in ('Male', '1', 'Female','2') THEN 1 ELSE 0 END) as N_missing,
SUM (CASE WHEN gender not in ('Male', '1', 'Female','2') THEN 1 ELSE 0 END) as m2
from gemd.Respondents_tidy

The code works without any error. I assume the columns of N_missing and m2 have same number. But they are not. N_missing return the correct number, but m2 give me 1 only. where is the problem of my coding? Does it means "not in" doesn't work?
I prefer the m2 way to calculate the result since it can reduce coding and easier to read.

Thanks,

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Viorel 126.1K

Check another duplicate answer too:

count(case when gender is null or gender not in ('Male', '1', 'Female', '2') then 1 end) as m2

Answer 2

Guoxiong 8,216

Try this:

SELECT COUNT(*) - SUM(CASE WHEN gender IN ('Male', '1', 'Female', '2') THEN 1 ELSE 0 END) AS N_missing,
       SUM(CASE WHEN gender NOT IN ('Male', '1', 'Female', '2') THEN 1 ELSE 0 END) AS m2
FROM gemd.Respondents_tidy;

Kai Yang 166 Reputation points

2021-08-16T21:11:18.127+00:00

the N_missing works well. Thank you,
But I still want to get m2 working.

Guoxiong 8,216

I think the gender column is nullable. If that is a case, try this:

SELECT COUNT(*) - SUM(CASE WHEN gender IN ('Male', '1', 'Female', '2') THEN 1 ELSE 0 END) AS N_missing,
       SUM(CASE WHEN ISNULL(gender, 'x') NOT IN ('Male', '1', 'Female', '2') THEN 1 ELSE 0 END) AS m2
FROM gemd.Respondents_tidy;

Kai Yang 166 Reputation points

2021-08-16T21:57:19.69+00:00

I did check. there is not any null value in gender column, but now, your code of m2 works well. thank you. below show you the frequency of gender table.

2 3995
NULL 0
Male 8725
Female 10271
na 1
1 4371
Viorel 126.1K Reputation points

2021-08-16T22:01:21.14+00:00

You probably accepted an answer incorrectly. Please check if you are actually using the GuoxiongYuan's answer.
Guoxiong 8,216 Reputation points

2021-08-16T22:04:01.227+00:00

So there is one NULL value. When you check if there is NULL, you need to use gender IS NULL instead of gender = NULL.

Answer 3

Erland Sommarskog 131.4K MVP Volunteer Moderator

Can gender be NULL? Keep in mind that all comparisons with NULL yields UNKNOWN, because NULL is an unknown value. So if you say:

WHEN gender not in ('Male', '1', 'Female','2')

and gender is NULL, we don't know if it different from Male, since it by chance that NULL could actually be Male. That's the theory. In practice, it may be different.

For this reason, you always need to keep NULL in mind. For this particular case, the easiest way out is:

WHEN isnull(gender, '') not in ('Male', '1', 'Female','2')

isnull means that you will get back the emtpy string when gender is NULL.

Kai Yang 166 Reputation points

2021-08-16T21:49:14.417+00:00

I did checking, the column gender doesn't have null. but this is good to know NULL yields UNKNOWN
Erland Sommarskog 131.4K Reputation points MVP Volunteer Moderator

2021-08-16T21:55:41.457+00:00
Hm, if so, that's a little strange. Try this query and see if something lights up:

select gender, CASE WHEN gender not in ('Male', '1', 'Female','2') THEN 1 ELSE 0 END from gemd.Respondents_tidy
Guoxiong 8,216 Reputation points

2021-08-16T22:00:49.173+00:00

It does not matter whether or not there is NULL value, it is a good practice to use ISNULL function if the column is nullable. Did you try my second query? Does it get the result correctly?
Kai Yang 166 Reputation points

2021-08-16T22:58:03.66+00:00

sorry, I forgot to tell you. the second query works. thank you
Kai Yang 166 Reputation points

2021-08-16T23:00:34.433+00:00

the 2nd column are all 0, no mater any value in gender column

Share via

question about "not in"

2 additional answers

Your answer