COUNT CASE and WHEN statement in MySQL

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0