SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Group by all months

    Hi

    Can anybody expand on this code to include zero's for the missing months

    http://www.sqlhacks.com/index.php/Dates/GroupByMonth

    If you have a look at the heading on the page (at the bottom) "Right way of grouping by month in SQL Server" It only returns a result list for months that have units sold. but how about including all months.. e.g.

    Code:
    Year        Month       Units sold
    ----------- ----------- ---------------------------------------
    2004        1           0.00
    2004        2           6.00
    2004        3           5.00
    After doing some googling i read that I need to join to another table containing a list of all the months? But do I also need a table with all the years?


    I am using MySQL

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you want to do it in pure SQL, then yes, creating those dummy tables is an easy solution.

    However, why do you want to do it in SQL? You can easily handle displaying the 0's in your application. The application is where you format output, not in database queries.

  3. #3
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    good point ! I could have solved this already if I had just done it in the php code, rather than all the time I wasted playing around with SQL !

  4. #4
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but as a matter of interest, how would one do it using SQL?

  5. #5
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would have a table of months use the appropriate join (left or right) to your result set, using some function such as IsNull() or Coalesce() (depending on the DBMS) to convert nulls into 0s.

    Cheers,
    D.

  6. #6
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you point to any link that shows how this is done, or provide the code yourself.

    I can't find a single example of how to do this but will check my "simply sql" book when i get home, it might be in there.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by chumba View Post
    ... it might be in there.
    it isn't

    i recommend that you use a numbers table --
    Code:
    CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY);
    INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),...
    make sure you have enough numbers to cover the largest range of year/months

    now you can write a query like this --
    Code:
    SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
      FROM numbers
     WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01
    or like this --
    Code:
    SELECT EXTRACT(YEAR_MONTH FROM mydate) AS yyyymm
      FROM ( SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
               FROM numbers
              WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01 ) AS d
    depending on how you want to use the data

    make sense?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, i'm with you so far... how about the last step of joining the tables together to output the data?

    TIA

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what does your table look like? is there a date field?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy, I'm using the example data from
    http://www.sqlhacks.com/index.php/Dates/GroupByMonth

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT EXTRACT(YEAR_MONTH FROM mydate) AS yyyymm
         , SUM(sales.sales_qty) AS units_sold
      FROM ( SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
               FROM numbers
              WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01' ) AS d
    LEFT OUTER
      JOIN sales
        ON sales.sales_date >= d.mydate
       AND sales.sales_date  < d.mydate + INTERVAL 1 MONTH
    GROUP
        BY yyyymm
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy, i'll give it a try and see what happens !

    Then i can hopefully modify it to fit my own application.

  13. #13
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    someone has this problem solved?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by yodlan View Post
    someone has this problem solved?
    yeah, me
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's amazing how many forum threads there are with people asking about this kind of query. None of them had any proper solution.

    Rudy. maybe some good content for "simply sql 2"

  16. #16
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chumba View Post
    It's amazing how many forum threads there are with people asking about this kind of query. None of them had any proper solution.

    Rudy. maybe some good content for "simply sql 2"
    Or a SQL reference book (details of SQL functions, which database servers support them, etc).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •