How to count NULL type values in Mysql

Question

I have a simple table with the answers, in this table I can have answers with value null, 0, 1, 2, 3, ... , only when I count the number of responses with each value these with the value null does not count, brings with zero amount.

Follow an example on sqlfiddle but follows the data, query and result obtained here too.

CREATE TABLE IF NOT EXISTS `respostas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_select` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;


INSERT INTO `respostas` (`id`, `option_select`) VALUES
(1, 1),
(2, NULL),
(3, 2),
(4, 1),
(5, 2),
(6, 3),
(7, NULL),
(8, 1),
(9, 0),
(10, 0),
(11, 1);

The query I used to get the quantity by selected option is

SELECT option_select
FROM  `respostas` 
GROUP BY option_select

and the result obtained is as follows::

| option_select | qtde |
|---------------|------|
| null          | 0    |
| 0             | 2    |
| 1             | 4    |
| 2             | 2    |
| 3             | 1    |

while the expected would be the null have Qtde of 2

Answer 1

To solve this problem, we can use the function IFNULL which returns a numeric value or string, depending on the context in which it is used:

mysql> SELECT IFNULL(1,0);

-> 1  

mysql> SELECT IFNULL(NULL,10);

-> 10 

mysql> SELECT IFNULL(1/0,10);

-> 10  

mysql> SELECT IFNULL(1/0,'yes');

-> 'yes'  

---

So applying to your problem, we can do like this:

SELECT option_select, count(IFNULL(option_select,0))
  FROM  `respostas` 
 GROUP BY option_selec

and the result obtained:

| option_select | qtde |
|---------------|------|
|        (null) |    2 |
|             0 |    2 |
|             1 |    4 |
|             2 |    2 |
|             3 |    1 | 

Can test by Sqlfiddle if it meets your need.