Skip navigation
Currently Being Moderated

SQL Question actually. Select items for all 12 months

May 27, 2012 2:17 PM

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.

 

March484714.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

....

 
Replies
  • Currently Being Moderated
    May 28, 2012 5:28 AM   in reply to wmkolcz

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 30, 2012 12:58 AM   in reply to wmkolcz

    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

     
    |
    Mark as:
  • Currently Being Moderated
    May 30, 2012 2:34 PM   in reply to wmkolcz

    Use a left join.

     
    |
    Mark as:
  • Currently Being Moderated
    May 30, 2012 4:25 PM   in reply to wmkolcz

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 31, 2012 1:07 AM   in reply to wmkolcz

    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

     
    |
    Mark as:
  • Currently Being Moderated
    May 31, 2012 5:13 PM   in reply to wmkolcz

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 31, 2012 11:43 PM   in reply to Sreeindia

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 4, 2012 9:27 AM   in reply to wmkolcz
    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points