Title: Why is my NULL count returning 0 in MySQL? Very basic
Tags: mysql
Question: I have an 2 easy questions that are puzzling me (I am new to Mysql)

I have a table (survey) with 3 columns, UserID (primary), SkinColor, and HairColor. I wanted to count the number of people who have a hair color that is null (they didn't answer the survey).

I did this with:

```select count(id)
from survey
where haircolor is null
```

But I was puzzled why I couldn't do

```select count(haircolor)
from survey
where haircolor is null
```

It returned 0. Why is this?

Question 2: I want to return the total number of survey respondents (all ID's), number of people with null value for hair color, and null value for skin color. I tried this query

```select count(id), count(skincolor), count(haircolor)
from survey
where skincolor is null and haircolor is null
```

But that just returned the the count where skincolor and haircolor are both null obviously, and not an individual count for each column. Is there a way to put the WHERE constraint up in the SELECT section so I can specify different constraints for each select? 

Thanks!

Comment: Q1: Use `select count(1) from ...`, it's faster and doesn't have the null problem.  Q2: It seems to me that you have the germ of the answer to question 2 in your first question.  How about `select count(1), count(skincolor), count(haircolor) from survey;`?


Here is another answer: for the second case:

```select 'No haircolor', count(id)
from survey
where haircolor is null
union all 
select 'no skincolor',count(id)
from survey
where skincolor is null
```

this should return something like:

```No haircolor   3
No skincolor   1
```

If you want the result as a single line you'll need to do a pivot on the result above.

Note: I'm assuming String literals are quoted with single quotes as in MsSql


Here is another answer: As for question 2:

It is easy enough, since COUNT(null) is 0:

``` select count(id), count(skincolor), count(haircolor) from survey
```

This gives you the number of non-nulls for each column.

You should also be able to do something along the lines of:

```SELECT count(1) AS num, 
       count(1)-count(skincolor) AS nullcolor, 
       count(1)-count(haircolor) AS nullhair 
    FROM survey
```


Here is another answer: I did this with:

```
  select count(id)
  from survey
  where haircolor is null
```

But I was puzzled why I couldn't do

```
  select count(haircolor)
  from survey
  where haircolor is null
```

It worked the first time because the first time you are counting the ID's where X is null and the IS's themselves are not null. The second time you are counting the things that are themselves NULL and the COUNT of things that are NULL is 0.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2012:00:10/13/12', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql>', 'Title': 'Why is my NULL count returning 0 in MySQL? Very basic', 'ViewCount': 119, 'id': 12876858, 'user_AboutMe': None, 'user_AccountId': 1936585.0, 'user_DisplayName': 'Sam Dayton', 'user_Reputation': 31.0, 'user_Views': 0.0, 'user_id': 1743905.0}}