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!

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

If you have an auxiliary table called nums with integers from 0 to 9, you can generate unbroken sequences of any type. Your issue is not that the count is null for a date value, it's that the date value doesn't exist at all. So say you want monthly counts between January 2004 and Mar 2006, you can create a temporary date list using the nums table like this:

SELECT DISTINCT ADDDATE('2004-01-01',INTERVAL i.i+j.i+k.i MONTH) AS mydate
FROM nums i JOIN nums j  JOIN nums k ORDER BY mydate LIMIT 27;

Then as described elsewhere you join your real data to the date list ON (year=year AND month=month).

Here's a similar query done on my own table (msds) for illustration:

select year(mydate) theyear, monthname(mydate) themonth, coalesce(c,0) thecount
from

(select DISTINCT adddate('2004-01-01',INTERVAL i.i+j.i+k.i MONTH) as mydate
FROM ints i  JOIN ints j  join ints k ORDER BY mydate LIMIT 27) datelist

left join

(SELECT year(issue_date) as y, month(issue_date) as m, count(*) c FROM msds m where issue_date between '2004-01-01' and '2006-03-01'
group by y, m) mydata

on (year(mydate)=y and month(mydate)=m)

Expanding on OMG Ponies statement, you need a Numbers or Tally table which includes a sequential list of integers that covers your months and years for all years on which you want to query.

Create Table Numbers ( Value int not null Primary Key )
Insert Numbers(Value) Values( 1 )
Insert Numbers(Value) Values( 2 )
...
Insert Numbers(Value) Values( 12 )
...
Insert Numbers(Value) Values( 2000 )
Insert Numbers(Value) Values( 2001 )
...
Insert Numbers(Value) Values( 2011 )
Insert Numbers(Value) Values( 2012 )

This would be a one-time insert and the table would remain static until such time as you needed more months or years. With that, we now Left Join your Projects table to the Numbers table:

Select Years.Value As PYear
    , Month_Name( Date_Add('2000-01-01', Interval Months.Value - 1 MONTH) ) As PMonth
    , Count( P.NonNullableCol ) As PNum
From Numbers As Months
    Cross Join Numbers As Years
    Left Join Projects As P
        On Year( P.PEnddate ) = Years.Value
            And Month( P.PEndDate ) = Months.Value
Where Months.Value Between 1 And 12
    And Years.Value Between 2008 And 2011
Group By Years.Value, Months.Value

Addition

Per comments, we are not told the nature of the underlying data. However, if the values in question were dates and not dates and times, then a faster approach would be a Calendar table rather than a Numbers table. Like a Numbers table, this would a static table of sequential dates covering the time period of dates in your Projects table.

Create Table Calendar ( DateValue date not null Primary Key )
Insert Calendar( DateValue ) Values( '2000-01-01' )
Insert Calendar( DateValue ) Values( '2000-01-02' )
Insert Calendar( DateValue ) Values( '2000-01-03' )
...
Insert Calendar( DateValue ) Values( '2011-03-01' )

Select Year( C.DateValue ) As PYear
    , Month( C.DateValue ) As PMonth
    , Count( P.NonNullableCol ) As PNum
From Calendar As C
    Left Join Projects As P
        On P.PEndDate = C.DateValue
Where C.DateValue Between '2008-11-01' And '2011-02-28'
Group By Year( C.DateValue ), Month( C.DateValue )