I am trying to create a reporting query where I SUM up all the amounts in the database for all 12 mont and come up with a result even if there isn't an amount totalled. I created a table called monthsInYear that has the ID (unique identifier) 1-12 and the months Jan - Dec. I want to SUM up the amounts per month and come up with 12 results (one per month).

I tried the query below but it only comes up with 1 month and all the amounts totalled to it.

March

484714.13

I am using:

SELECT monthsInYear.month, IFNULL( SUM(income.amount), 0 ) AS amount

FROM monthsInYear

LEFT OUTER JOIN income ON EXTRACT(MONTH FROM income.dateAdded) = monthsInYear.monthID

WHERE income.orgID = 10031

AND YEAR(income.DateAdded) = '2011'

ORDER BY EXTRACT(MONTH FROM income.dateAdded) ASC

Can anyone tell me what I am doing wrong. I would much much prefer something like:

But when I run this, it gives me total for only the months that have a total and not the 0 for the ones that come up as null. Any ideas?

....

Also tried

SELECT m.month, IFNULL( SUM(i.cost), 0 ) AS amount

FROM getExpenses i LEFT OUTER JOIN monthsInYear m

ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)

WHERE orgID = 10031

AND YEAR(i.dateAdded) = '2011'

GROUP BY m.month

ORDER BY EXTRACT(MONTH FROM i.dateAdded) ASC

There are two problems. First, the tables in your join are reversed. A LEFT JOIN returns all records from the left hand table (expenses) even if there are no matching records in the right hand table. What you want is the opposite. So the months table must on be on the left side of the JOIN:

...

FROM monthsInYear m LEFT JOIN getExpenses i ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)

Second, when there is no matching expense record, the column values will all be null. So as soon as you use one of the expense fields in your WHERE clause, ie

WHERE i.orgID = 10031

AND YEAR(i.dateAdded) = '2011'

... those missing records are dropped, because a null value is never equal to anything. So you are negating the outer join altogether. You need to move those conditions into the JOIN clause instead: