Do you have some zero dates? Datetime values of 0000-00-00 00:00:00
are considered by MySQL to simultaneously satisfy is null
and is not null
:
steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)
steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
See: http://bugs.mysql.com/bug.php?id=940
This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.
Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null
and is not null
counts should exceed the unrestricted count)...
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
If you would like the counts summed up, include WITH ROLLUP
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
Best Answer
Given this sample data:
One way to solve it is by taking the count separately:
Or slightly differently:
Another is along the lines of Rob's suggestion: