Thread: Query Help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    7
    Rep Power
    0

    Question Query Help


    I'm not a query expert by any means, so I need some help with this query....

    I have a table, in which 7 records meet the date criteria of:
    completed_stamp BETWEEN '2017/7/24 00:00:00 AM' AND '2017/7/30 11:59:59 PM'

    These are those records:

    ID COMPLETED_STAMP
    606 7/24/2017 7:50:01 AM
    608 7/26/2017 2:20:24 AM
    613 7/28/2017 9:44:20 AM
    626 7/28/2017 9:44:08 AM
    627 7/28/2017 9:43:57 AM
    628 7/28/2017 9:43:47 AM
    629 7/28/2017 9:43:38 AM

    I'm splitting them up into the weeks of the year, and this is my query:
    SELECT week(stamp) as weeknum, count(*) AS mycount, YEAR(stamp) as theyear FROM TABLE WHERE completed = 1 AND completed_stamp BETWEEN '2017/7/24' AND '2017/7/30' GROUP BY week(stamp)

    But this is the result I'm getting:
    weeknum mycount theyear
    29 3 2017
    30 4 2017

    I need it to show:
    weeknum mycount theyear
    30 7 2017

    Not sure why it's show 3 of those records in week 29 - they are all in week 30 of 2017

    Any help greatly appreciated! (I'll buy you a beer next time your near Allentown, PA) !!!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    what is the datatype of completed_stamp?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,131
    Rep Power
    2011
    Is stamp an alias for completed_stamp?
    Is completed_stamp defined as datetime?
    Your GROUP BY should also include YEAR(stamp), despite that MySQL is more open on this point.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    what is the datatype of completed_stamp?
    It's a DATETIME type in MySQL.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    7
    Rep Power
    0
    Found the problem with your help! "Stamp" should be "completed_stamp" throughout the query....thank you all!

IMN logo majestic logo threadwatch logo seochat tools logo