Ok...maybe not. Seems the IFNULL isn't working. I have a view 'getExpenses' that unions a bunch of sources to produce a list of expenses. The view works perfectly
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?
SELECT monthsInYear.month, monthsInYear.monthID,IFNULL(Sum(getExpenses.cost), 0 ) AS amount
FROM monthsInYear, getExpenses
WHERE EXTRACT(MONTH FROM getExpenses.dateAdded) = monthsInYear.monthID
AND getExpenses.orgID = 10031
AND YEAR(getExpenses.DateAdded) = '2011'
GROUP BY monthsInYear.month, monthsInYear.monthID
ORDER BY monthsInYear.monthID ASC
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
WHEN I DO a direct query, it does come up as NULL (Since there is no value for April)
SELECT SUM(cost) FROM getExpenses
WHERE EXTRACT(MONTH FROM dateAdded) = '4'
AND orgID = 10031
| 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:
SELECT m.month, IFNULL( SUM(i.cost), 0 ) AS amount
FROM monthsInyear m LEFT OUTER JOIN getExpenses i
ON m.monthID = EXTRACT(MONTH FROM i.dateAdded)
AND i.orgID = 10031
AND YEAR(i.dateAdded) = 2011
GROUP BY m.month
ORDER BY m.month
AdChoices