How to count values of type NULL in MySql

Navigation
1

I have a simple table with the answers, in this table I can have answers with value null, 0, 1, 2, 3, ... , but when I count the number of answers with each value those with the value null does not count, / p>

Here's an example in sqlfiddle but follow the data, query and result you get 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 per selected option is 

SELECT option_select
FROM  'respostas' 
GROUP BY option_select

and the result is as follows: 

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

While expected would be null ter qtde of 2

    
asked by anonymous 04.03.2016 / 20:10

1 answer

3

To resolve this problem, we can use the IFNULL that 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 by applying to your problem, we can do this: 
SELECT option_select, count(IFNULL(option_select,0))
  FROM  'respostas' 
 GROUP BY option_selec

and the result: 

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

You can test for SQLFiddle to meet your need.

    
04.03.2016 / 20:37
Program that opens Notepad in the background [closed] Difference between Render Partial and Render Action