Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
---------------------------------------------
share|improve this question

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.

share|improve this answer

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
share|improve this answer
    
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 at 9:41

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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