COUNT CASE and WHEN statement in MySQL

Question

How to use COUNT CASE and WHEN statement in MySQL query, to count when data is NULL and when it is not NULL in one MySQL query?

`COUNT(col)` = Number of `NOT NULL`. `COUNT(*) - COUNT(col)` = Number of `NULL` Why do you need `CASE`? — Martin Smith, Feb 18 '11 at 18:31

score 111 · Accepted Answer · answered Feb 18 '11 at 18:32

111

Use:

SELECT SUM(CASE 
             WHEN t.your_column IS NULL THEN 1
             ELSE 0
           END) AS numNull,
       SUM(CASE 
             WHEN t.your_column IS NOT NULL THEN 1
             ELSE 0
           END) AS numNotNull
  FROM YOUR_TABLE t

That will sum up the column NULL & not NULL for the entire table. It's likely you need a GROUP BY clause, depending on needs.

answered Feb 18 '11 at 18:32

OMG Ponies

325,700
82
523
502

And now how to echo this values, how much is NULL and how much is not NULL. – tanasi Feb 18 '11 at 18:37
@Ivan Tanasijevic: There're examples in the [mysql_query documentation](http://php.net/manual/en/function.mysql-query.php) – OMG Ponies Feb 18 '11 at 18:43
If you want to achieve that with `COUNT` you can use null (not counted) and another arbitrary value (counted). – Kiril Feb 06 '15 at 09:25
1

Please note that SUM will return NULL if there are no rows selected (i.e. the table is empty or a where condition excludes all rows). You can still obtain a "0" by coding the select this way: IFNULL(SUM(...), 0) – Daniels118 May 30 '20 at 08:48

score 3 · Answer 2 · answered May 30 '20 at 08:59

You can exploit the fact that COUNT only counts non-null values:

SELECT COUNT(IFNULL(t.your_column, 1)) AS numNull,
       COUNT(t.your_column) AS numNotNull
  FROM YOUR_TABLE t

Another approach is to use the fact that logical conditions get evaluated to numeric 0 and 1, so this will also work:

SELECT IFNULL(SUM(t.your_column IS NULL), 0) AS numNull,
       IFNULL(SUM(t.your_column IS NOT NULL), 0) AS numNotNull
  FROM YOUR_TABLE t

Please note that SUM will return NULL if there are no rows selected (i.e. the table is empty or a where condition excludes all rows), this is the reason for the IFNULL statements.

COUNT CASE and WHEN statement in MySQL

2 Answers2

Linked