SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query - WHERE by date

    Hey,

    I have a column in one of my tables called "dateline" that is of type DATE. Now, I am trying to perform a select * from this database, and group by year-month, so all the entries for say 2007-10-xx would be grouped together and like so, but I can't figure out how to do this. I tried the following, but it doesn't work:

    PHP Code:
    $Query mysql_query("SELECT COUNT(billID) AS totalCount, dateline FROM billofsale GROUP BY Date('Y-m', dateline)"); 
    I'm using php, btw. Any help?
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it sounds like you want ORDER BY, not GROUP BY

    GROUP BY is for collapsing sets of multiple rows into one row

    this is completely at odds with your requirement to perform a select *
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically I want to perform the equivalent of a SELECT DISTINCT(dateline) if dateline was formated like yyyy-mm and not yyyy-mm-dd - so I want to ignore the days. And then get the count of each group - like, if there were 7 records that were of 2007-10-xx, 3 records of 2006-04-xx and 1 record of 2002-05-xx, it would return:

    2007-10: 7
    2006-04: 3
    2002-05: 1
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ah, you do want an aggregation!!

    that dreaded, evil "select star" thing was just a diversion, eh?
    Code:
    SELECT EXTRACT(YEAR_MONTH FROM dateline) AS yyyy_mm
         , COUNT(*)                          AS totalCount
      FROM billofsale
    GROUP
        BY yyyy_mm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, works great

    This may be a stupid question, but i've tried a couple of what I thought would be the obvious solutions and all it produced was errors, but as it is now if I were to print out the results it would look like:

    200803 - 1
    200804 - 3

    But i'm trying to get it so there is a dash between the year and month, like 2008-03. Any help?
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT CONCAT(YEAR(dateline)
                 ,'-'
                 ,MONTH(dateline)) AS yyyy_mm
         , COUNT(*)                AS totalCount
      FROM billofsale
    GROUP
        BY YEAR(dateline)
         , MONTH(dateline)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    Hate to be a newb again, but is there anyway I can get it to print out the month in the xx format? What it does now is 2008-4, where I want it to print out 2008-04.
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what programming language are you using to display the query results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP

    I tried: Date("Y-m", $Row['yyyy_mm']), but that screwed up the date big time (it prints out as 1969-12)
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    for maximum flexibility, get mysql to return a date column, e.g. the 1st of the month, and then you can use whatever php date formatting function you want

    as it stands, the query has been producing character strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •