Counting Null values in MYSQL

Question

hi,

How do i count null values while making cross tab query?

I have a table with three colums [id, name, answer]

i have following records:

ID  NAME   ANS
1   ABC    1
1   ABC    0
1   ABC    NULL
2   XYZ    1
2   XYZ    NULL
2   XYZ    NULL
2   XYZ    1
2   XYZ    0
1   ABC    0

now i would like to get my result:

ID  Name   NULLCOUNT     TRUE COUNT   FALSE COUNT
1   ABC    1             1            2
2   XYZ    2             2            1

I am using following SQL Statement:

select ID, NAME, 
    sum(case ANS when null then 1 else 0 end) as NULLCOUNT,
    sum(case ANS when 1 then 1 else 0 end) as TRUECOUNT,
    sum(case ANS when 0 then 1 else 0 end) as FALSECOUNT
from 
    TBL1
 Group By ID, Name

Getting my result:

ID  Name   NULLCOUNT     TRUE COUNT   FALSE COUNT
1   ABC    0             1            2
2   XYZ    0             2            1

The NULL Count is getting error. Why and how can i solve this?

Answer 1

I believe instead of this:

 sum(case ANS when null then 1 else 0 end) as NULLCOUNT

You should use this:

 sum(case when ANS is null then 1 else 0 end) as NULLCOUNT

Answer 2

null -> is null?

Answer 3

NULL doesn't even compare with itself, you could use "CASE WHEN ANS is NULL"(you're also missing GROUP BY). Or try:

select ID, NAME, 
    sum(if(ans IS NULL, 1, 0)) as NULLCOUNT,
    sum(case ANS when 1 then 1 else 0 end) as TRUECOUNT,
    sum(case ANS when 0 then 1 else 0 end) as FALSECOUNT
from 
    TBL1
group by ID,NAME