We're trying to figure out how to return counts (or sums) for each month in the last 12 months (or any defined period). The problem is that we can't get mysql to return '0' for the months that don't have any records.

EG, What we're looking for is:

jan: 0
feb: 0
mar: 23
apr: 344
may: 0
jun: 2
jul: 33
aug: 0
sep: 0
oct: 98
nov: 235
dec: 442

but the following does not cut the mustard and just doesn't return counts for months that dont have any records:

SELECT
MONTHNAME(rd_date) as monthname,
month(rd_date) as month2,
sum(rd_abs) AS co2
FROM
reporting_data
WHERE
year(rd_date) = '2008'
GROUP BY monthname
ORDER BY month2 ASC

One thought was to iterate over a 12month array in php.

Any help appreciated. Its got us scratching our heads somewhat.

Thx in advance.
N.