SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by date, not date and time

    I have a datetime field, entryDate. I need to group by just the date of the date time. So it groups all the records from the same day together. What is the best way to do this?
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Try this:

    SELECT * FROM table GROUP BY DATE_FORMAT(entryDate, '%Y-%m-%d')

    /Erik

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...or :

    SELECT * FROM table GROUP BY TO_DAYS(entryDate)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please don't say "select star" when you use GROUP BY

    you could get unpredictable results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, what do you mean?

    Yours, Erik.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    because it's illegal in all other databases, and can produce unpredictable results in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    How should the query look to be correct?

    Yours, Erik.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select date_format(entryDate,"%Y-%m-%d") as printDate
         , count(*) as rows
      from yourtable
    group
        by date_format(entryDate,"%Y-%m-%d")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2004
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    please don't say "select star" when you use GROUP BY

    you could get unpredictable results
    Does that also apply to ORDER BY, or is just a problem inherent to queries using the GROUP BY command?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    just GROUP BY

    the deal is, every non-aggregate column in the SELECT must be in the GROUP BY, and vice versa

    but the columns in the ORDER BY can be a subset of the columns in the SELECT list, and the ORDER BY can also include aggregates

    this means you can say "select star" with or without an ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    May 2004
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks for clearing that up.


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
  •