How do I display the number of records per month (including zero) from a MySQL Database?

Question

AMENDED 24/11/2012 based on comments below.

I have a MySQL database (v5.0.95) of members which lists when they joined

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `joined` datetime NOT NULL,
  KEY `id` (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I also have a second table which will have records indicating which YYYY-MM

CREATE TABLE IF NOT EXISTS `blocker` (
    `group_id` int(11) NOT NULL,
    `YYYYMM` char(7) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Ideally the following join would provide the following

  SELECT date_format( m.joined, '%Y-%m' ) AS DateJoined, count( * ) AS NumJoined 
  FROM members AS m
  LEFT OUTER JOIN blocker AS b
  ON b.YYYYMM = date_format( m.joined, '%Y-%m')
  WHERE m.group_id =1637017 AND b.group_id =1637017
  GROUP BY DateJoined
  ORDER BY DateJoined ASC

would give me this

DateJoined  NumJoined 
2012-01     0
2012-02     0
2012-03     0
2012-04     17
2012-05     0
2012-06     12
2012-07     10
2012-08     10
2012-09     11
2012-10     14
2012-11     4

unfortunately it is not providing zero result months and gives me this

DateJoined  NumJoined 
2012-04     17
2012-06     12
2012-07     10
2012-08     10
2012-09     11
2012-10     14
2012-11     4

Any pointers would be appreciated. Am I close...?

possible duplicate of [Creating a date range table](http://stackoverflow.com/questions/10374380/creating-a-date-range-table) — dnagirl, Nov 14 '12 at 17:14

score 3 · Answer 1 · answered Nov 14 '12 at 17:12

sql databases can't produce data for you where it doesn't exist. if no one joined in a particular month, you can't have it magically produce that month out of nothing.

if you want to force it, you'll have to have a temp table with the individual months listed in the range you desire, then you can join against that temp table and get your 0-counts.

score 0 · Answer 2 · answered Nov 14 '12 at 17:14

0

You could create a Year/Month table and use a left join to the member table.

answered Nov 14 '12 at 17:14

Tom

6,593
3
21
42

score 0 · Answer 3 · answered Nov 15 '12 at 07:19

create another table (may be named as dateCalender) storing values for each month as follows:

now retrive results using a left outer join of this new table with your original table, for months having valid values will return the values, for months not having any values, it will return Null (that can be represented as 0)

How do I display the number of records per month (including zero) from a MySQL Database?

3 Answers3