Take the 2-minute tour ×
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. It's 100% free, no registration required.

how to count and retrieve null and not null on same column from 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 |
---------------------------------------------------

result should be like this

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

Please help. Thanks in advance

share|improve this question
    
SUM(CASE WHEN visted IS NULL THEN 1 ELSE 1 END) as register.. –  Mihai May 11 at 14:28
    
@Mihai you have a typo... ELSE 0 and the alias is not visited –  Serpiton May 11 at 18:00

1 Answer 1

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
    
count(registDate) will ignore NULLs –  Mihai May 11 at 22:17
    
You need a GROUP BY –  ypercube May 11 at 22:30
    
@Mihai - that should be the desired behaviour. –  Yawar May 11 at 23:02
    
@ypercube - thanks! Usually the SQL engine reminds me :-) –  Yawar May 11 at 23:03

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.