Group by month

[Jonob]

Lets say I have a (simplified) mysql query as follows:

 

SELECT DATE_FORMAT(i.date,'%c') as income_month, DATE_FORMAT(i.date,'%Y') as income_year, 
   sum(i.amount_ex) as income_amount
FROM income i
WHERE i.date>= "2008-01-01" AND i.date <= "2008-03-31"
GROUP BY income_month, income_year

 

Basically, I am grouping by month number and year between a specific date range.

 

Is there any way for me to get all months returned in my date range (with a zero for the income_amount alias), even if no data actually exists for each month?

 

For example, lets say that we have data in Jan and Feb, but not in March, so the sql returns something like:

 

income_month, income_year, income_amount

1, 2008, 100

2, 2008, 200

3, 2008, 0  <-- This is the row that the sql auto creates

[Mchl]

Try this

 

SELECT MONTH(i.date) as income_month, YEAR(i.date) as income_year,
   IFNULL(sum(i.amount_ex),0) as income_amount
FROM income i
WHERE i.date>= "2008-01-01" AND i.date <= "2008-03-31"
GROUP BY income_month, income_year