patternsqlModerate

Count null and not null values in a column

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

valuescolumnnullandcountnot

Problem

How to count and retrieve null and not null on same column in MySQL?

mytable

---------------------------------------------------
id   |    name    |      visited   |   registDate |
---------------------------------------------------
1    |    george  |       NULL     |   2014-04-01 |
---------------------------------------------------
2    |    Thomas  |       NULL     |   2014-04-15 |
---------------------------------------------------
3    |    Wilfred |        1       |   2014-04-24 |
---------------------------------------------------
4    |    paul    |        1       |   2014-04-10 |
---------------------------------------------------
5    |    elina   |       NULL     |   2014-05-03 |
---------------------------------------------------
6    |    angela  |       NULL     |   2014-04-13 |
---------------------------------------------------
7    |    elina   |        1       |   2014-05-18 |
---------------------------------------------------

Expected result

month      register    visited    not visited
---------------------------------------------
05-2014       2           1          1   
---------------------------------------------
04-2014       5           2          3
---------------------------------------------

Solution

Try

SELECT 
   DATE_FORMAT(registDate, '%m-%Y') AS month,
   COUNT(name) AS register,
   SUM(!ISNULL(visited)) AS visited,
   SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(registDate, '%m-%Y');

No need to create another column.

Code Snippets

SELECT 
   DATE_FORMAT(registDate, '%m-%Y') AS month,
   COUNT(name) AS register,
   SUM(!ISNULL(visited)) AS visited,
   SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(registDate, '%m-%Y');

Context

StackExchange Database Administrators Q#64927, answer score: 14

Revisions (0)

No revisions yet.