#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96

    Getting all the months from distinct years


    Hi--

    I'm really at a loss here. I need to pull the months and years from blog post entries, but can only get so far.

    I need to pull the years from all the posts as DISTINCT values; that's no problem, but I also need to pull all the months entries were posted for each year.

    For example, if I have 200 posts in 2011, but none of them are in October, how would I query that?

    This is as far as I've gotten and it's not returning what I need:

    Code:
    SELECT DISTINCT(DATE_FORMAT(posted_date, '%Y')) AS posted, GROUP_CONCAT(DATE_FORMAT(posted_date, '%M')) AS posted_month FROM news ORDER BY posted DESC
    Do I need a subquery for this or should I just run this as two queries?

    Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    What would you want the output to look like? I can follow most of what you are saying but get lost as to how you perceive the results. For a start you'll almost certainly needing to be using GROUP BY on the year component of the date (and drop the DISTINCT).
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    I'm building a horizontal menu with years across the top and dropdowns containing the months for each year.

    Naturally, I only want to include the months for years that have posts.

    What I need is the distinct months for each distinct year if that makes sense.

    Sorry for being confusing.
    Last edited by Frank Grimes; July 11th, 2013 at 01:18 PM.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by Frank Grimes
    What I need is the distinct months for each distinct year if that makes sense.
    yes, it does, and it's frighteningly easy...
    Code:
    SELECT DISTINCT
           YEAR(posted_date) AS yyyy
         , MONTH(posted_date) AS mm
      FROM news
    neat, eh?

    Comments on this post

    • Frank Grimes agrees : you rule!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    Originally Posted by r937
    yes, it does, and it's frighteningly easy...
    Code:
    SELECT DISTINCT
           YEAR(posted_date) AS yyyy
         , MONTH(posted_date) AS mm
      FROM news
    neat, eh?
    HAHAHAHHA...that IS easy!



    I love that. Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo