Counting null data in MySQL

Question

let's say I have one table that has this data:

name       status
bob        single
bob        single
jane       null
tina       null
shane      married

I want if the status "single or data null" means single. therefore, if the data is empty script can consider them single and can be counted together. so I can show the result as:

Single     Married
3            1

I tried with this and it does not work:

SELECT SUM(IF(status='single',1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

Answer 1

Using:

SELECT SUM(CASE WHEN x.status = 'single' OR x.status IS NULL THEN 1 ELSE 0 END) AS single,
       SUM(CASE WHEN x.status = 'married' THEN 1 ELSE 0 END) AS married
  FROM (SELECT DISTINCT
               t.name,
               t.status
          FROM YOUR_TABLE t) x

Answer 2

If you know that there are only “single”, “married” and “zero” options, this will work:

SELECT SUM(IF(status!='married',1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

Otherwise try

SELECT SUM(IF(status='single' OR status is null,1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

Answer 3

SELECT SUM(IF(status='single' OR status IS NULL,1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table