question

KaiYang-6525 avatar image
0 Votes"
KaiYang-6525 asked KaiYang-6525 commented

question about "not in"

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,

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Check another duplicate answer too:

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

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;
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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;
0 Votes 0 ·

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

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered KaiYang-6525 commented

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.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I did checking, the column gender doesn't have null. but this is good to know NULL yields UNKNOWN

0 Votes 0 ·

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?

1 Vote 1 ·

sorry, I forgot to tell you. the second query works. thank you

0 Votes 0 ·

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
0 Votes 0 ·

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

0 Votes 0 ·