13 Replies Latest reply: Jun 4, 2012 9:27 AM by -==cfSearching==- RSS

    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.

       

      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

      ....

        • 1. Re: SQL Question actually. Select items for all 12 months
          Dan Bracuk Community Member

          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.

          • 2. Re: SQL Question actually. Select items for all 12 months
            wmkolcz Community Member

            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

            • 3. Re: SQL Question actually. Select items for all 12 months
              BKBK MVP

              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

              • 4. Re: SQL Question actually. Select items for all 12 months
                wmkolcz Community Member

                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

                • 5. Re: SQL Question actually. Select items for all 12 months
                  wmkolcz Community Member

                  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

                  • 7. Re: SQL Question actually. Select items for all 12 months
                    wmkolcz Community Member

                    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

                     

                    June695.00
                    July71015.00
                    August8272.00
                    October10105.00
                    December12100.00
                    • 8. Re: SQL Question actually. Select items for all 12 months
                      Dan Bracuk Community Member

                      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.

                      • 9. Re: SQL Question actually. Select items for all 12 months
                        Sreeindia Community Member

                        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

                        • 10. Re: SQL Question actually. Select items for all 12 months
                          wmkolcz Community Member

                          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

                          • 11. Re: SQL Question actually. Select items for all 12 months
                            Dan Bracuk Community Member

                            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.

                            • 12. Re: SQL Question actually. Select items for all 12 months
                              BKBK MVP

                              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

                              • 13. Re: SQL Question actually. Select items for all 12 months
                                -==cfSearching==- Community Member
                                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