Copy link to clipboard
Copied
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:
January 3000
February 3000
March 4000
April 0
May 5000
June 6000
July 5465
....
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
...Copy link to clipboard
Copied
Are you sure your query is even running? You are requesting a field and a sum, but you don't have a group by clause.
Copy link to clipboard
Copied
Ahhhh..thanks. My orginal did have a group by but the example I found and modified did not. Thanks for the clue!
Wally Kolcz
Lead Application Developer
PetPal Manager
http://petpalmanager.com
586.808.2847
Copy link to clipboard
Copied
I would start from something this
SELECT monthsInYear.month, IFNULL(income.amount, 0 ) AS amount
FROM monthsInYear
WHERE EXTRACT(MONTH FROM income.dateAdded) = monthsInYear.monthID
AND income.orgID = 10031
AND YEAR(income.DateAdded) = '2011'
ORDER BY EXTRACT(MONTH FROM monthsInYear.month) ASC
Copy link to clipboard
Copied
Thanks! Solved it right after mentioning the group by statement was missing and it runs like a charm!
Wally Kolcz
Lead Application Developer
PetPal Manager
http://petpalmanager.com
586.808.2847
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Use a left join.
Copy link to clipboard
Copied
Didn't change the results unless I am missing something
SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount
FROM getExpenses e LEFT JOIN monthsInYear m
ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)
WHERE e.orgID = 10031
AND YEAR(e.dateAdded) = '2011'
GROUP BY m.month, m.monthID
ORDER BY EXTRACT(MONTH FROM e.dateAdded) ASC
June | 6 | 95.00 |
July | 7 | 1015.00 |
August | 8 | 272.00 |
October | 10 | 105.00 |
December | 12 | 100.00 |
Copy link to clipboard
Copied
Do a left join the way you did in the opening post. In other words, the table from which you want all the rows should be left of the word left.
Copy link to clipboard
Copied
Hi
As Dan Baruk rightly said try something like below
SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount
FROM monthsInYear m
LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)
WHERE e.orgID = 10031
AND YEAR(e.dateAdded) = '2011'
GROUP BY m.month, m.monthID
ORDER BY EXTRACT(MONTH FROM e.dateAdded) ASC
Here is a got article related to joins
http://www.devx.com/dbzone/Article/17403/1954
Regards
Sreekar
Copy link to clipboard
Copied
Unless I am missing something, it produces the same results. Only the months where a total is found. No 0's
Wally Kolcz
Lead Application Developer
PetPal Manager
http://petpalmanager.com
586.808.2847
Copy link to clipboard
Copied
It might be your grouping fields. Group by the non-aggregate fields in your select clause. Once again, I'm surprised the query actually ran the way you presented it.
Copy link to clipboard
Copied
Sreeindia wrote:
As Dan Baruk rightly said try something like below
SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount
FROM monthsInYear m
LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)
WHERE e.orgID = 10031
AND YEAR(e.dateAdded) = '2011'
GROUP BY m.month, m.monthID
ORDER BY EXTRACT(MONTH FROM e.dateAdded) ASC
I think it is sufficient to group by just one column, using the table to the left of the join, like this:
SELECT m.month, m.monthID, IFNULL(SUM(e.cost), 0) AS amount
FROM monthsInYear m
LEFT JOIN getExpenses e ON m.monthID = EXTRACT(MONTH FROM e.dateAdded)
WHERE e.orgID = 10031
AND YEAR(e.dateAdded) = '2011'
GROUP BY m.monthID
ORDER BY m.monthID ASC
Copy link to clipboard
Copied
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