MySQL COUNT - return zero result, not NULL

I am trying to return a resultset from MySql which is grouped by the YEAR and MONTH, and which has a count returned for every YEAR/MONTH..

Here is where I started:

SELECT YEAR(p.pEndDate) AS pYear, MONTHNAME(p.pEndDate) AS pMonth, count(*) AS pNum 
FROM projects p
WHERE p.status=3
GROUP BY YEAR(p.pEndDate), MONTH(p.pEndDate)

This SQL basically does 90% of what I need, except in the case that there is a month where the count is zero. For example, in 2009 July had zero projects with a status of 3, so I am getting:

2008    November    1
2009    January     2
2009    February    2
2009    March   2
2009    April   1
2009    May 2
2009    June    3
2009    August  2
2009    September   1
2009    October 1
2009    November    2
2009    December    1
2010    January 4
2010    February    1
2010    March   1
2010    April   3
2010    May 3
2010    June    3
2010    July    3
2010    August  3
2010    September   3
2010    October 2
2010    November    2
2010    December    3
2011    January 2
2011    February    1

Notice how July is just not there.

So I started doing some research with using another table to force the resultset to include July. So I created a new table 'monthTable' and added two columns monthID int Primary Key, monthName VARCHAR(3).

I've tried many different ways of using this table, starting with a RIGHT JOIN and so on.. none have them have yielded successful results, in fact almost everything I do yields the same result set as above.

Any help would be greatly appreciated!

Solution

I've tried many different ways of using this [monthTable] table, starting with a RIGHT JOIN and so on.. none have them have yielded successful results, in fact almost everything I do yields the same result set as above.

FROM projects p WHERE p.status=3

My guess is that you were trying something like this

FROM projects p
RIGHT JOIN monthTable m on <join p to m>
WHERE p.status=3`

The problem is that the WHERE clause will be filtering out any record that doesn't have any p.status values (null). You need to move such filters to the JOIN clause, like this

FROM projects p
RIGHT JOIN monthTable m on <join p to m> AND p.status=3`

Curious, but how does a table like suffice, esp with monthName being only varchar(3)?

monthID int Primary Key, monthName VARCHAR(3).

Try creating it like this instead (one-off)

DROP PROCEDURE IF EXISTS FillMonthsTable;

delimiter //
CREATE PROCEDURE FillMonthsTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists monthsTable;
  create table monthsTable (theYear int, theMonth int, monthName varchar(20));

  SET @x := date('2000-01-01');
  REPEAT 
    insert into monthsTable (theyear, themonth, monthname) SELECT year(@x), month(@x), monthname(@x);
    SET @x := date_add(@x, interval 1 month);
    UNTIL @x > date('2030-01-01') END REPEAT;
END//
delimiter ;

CALL FillMonthsTable;

DROP PROCEDURE FillMonthsTable;

Then using this query (1-pass to group your data, then a left join to produce the 0s)

SELECT m.theYear, m.theMonth, IFNULL(t.pNum, 0) theCount
FROM monthsTable m
LEFT JOIN (
    SELECT YEAR(p.pEndDate) AS pYear, MONTH(p.pEndDate) AS pMonth, count(*) AS pNum 
    FROM projects p
    WHERE p.status=3
    GROUP BY YEAR(p.pEndDate), MONTH(p.pEndDate)
) t on t.pYear = m.theYear and t.pMonth = m.theMonth
ORDER BY m.theYear, m.theMonth