8

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
---------------------------------------------
5

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.

2

The first thing to do is 'add on' a column for the month:

select *, date_format(registDate, '%Y-%m') as regist_month
from mytable

Then you can get all the counts:

select
  regist_month
, count(registDate) as count_registered
, sum(case when visited is not null then 1 else 0 end) as count_visited
, sum(case when visited is null then 1 else 0 end) as count_not_visited
from (
  select *, date_format(registDate, '%Y-%m') as regist_month
  from mytable
) group by regist_month
  • You can use count instead of sum and shorten the expression a bit: count(visited). count(<column>) will only count not null. If you add another level of nesting count_not_visited can be determined as count_registered - count_visited – Lennart Feb 29 '16 at 9:41
1

In order to count all the non null values for a column, say col1, you just may use count(col1) as cnt_col1. But, to be more obvious, you may use the sum() function and the IS NOT NULL operator, becoming sum(col1 IS NOT NULL). That's because the IS NOT NULL operator returns an int: 1 for true and 0 for false.

In order to count null values you can use the IS NULL operator, which returns 1 when the value null. Like before, with the sum() operator.

Given that, in order to get the registered, visited and not visited for each month, this is what you can do:

SELECT
date_format(registDate, '%m-%Y') as month,
count(registDate) as register,
sum(visited is not null) as visited,
sum(visited is null) as 'not visited'
GROUP BY
date_format(registDate, '%m-%Y')

Note that you could output the 'not visited' column with the space, just by quoting, double quoting or using backticks (`).

Another approach to select and group by month would be to concatenate month with year, like this concat(month(registDate), '-', date(registDate)). But it's less elegant.

The case operator proposed in other responses is perfectly valid, but I think it more adequate for other situations. And it's more verbose.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.