SQL Question actually. Select items for all 12 months

[wmkolcz]

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

....

[Dan_Bracuk]

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.

[wmkolcz]

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

[BKBK]

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

[wmkolcz]

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

[wmkolcz]

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

[Dan_Bracuk]

Use a left join.

[wmkolcz]

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

[Dan_Bracuk]

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.

[Sreeindia]

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

[wmkolcz]

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

[Dan_Bracuk]

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.

[BKBK]

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

[-__cfSearching__- · Accepted solution]

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