#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,799
    Rep Power
    529

    GROUP BY date question


    Let's say I have the following query. Is there any chance that myDate will fall outside of the given month/year associated with the returned COUNT(*)?

    Code:
    SELECT COUNT(*) AS total, myDate FROM myTable GROUP BY YEAR(myDate), MONTH(myDate)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    I'm not sure about what you mean with "fall outside". You don't have any WHERE or HAVING, so all records will be in some group and you'll see all groups.
    But I see a problem in the SELECT clause. Not-grouped expressions should not be SELECTed. I think you want something like:

    Code:
    SELECT COUNT(*), YEAR(myDate), MONTH(myDate) ...
    Also, if you have values like NULL or '0000-00-00' in myDate, maybe you want to exclude them with:

    Code:
    WHERE myDate > 0
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,799
    Rep Power
    529
    Hello f_razzoli from Italy! I was just there several months ago and had a wonderful time.

    Thank you for your reply.

    Not-grouped expressions should not be SELECTed
    Why not? What would happen?

    Let's say I run the following query:

    Code:
    SELECT COUNT(*), myDate, YEAR(myDate), MONTH(myDate)...
    Possible returned data will be something like:
    Code:
    ...
    213   2012-01-01  2012 01
    413   2012-02-03  2012 02
    242   2012-03-02  2012 03
    ...
    When I tested it, for each of the returned records, myDate always falls within the given month/year (and appeared to be the first record within the given period).

    My concern is that I am going to change indexes or something, and will no longer get this outcome.

    I also recognize that it is a little sketchy. The returned myDate is just one of many dates that fall within the range. It works perfect for my needs, but maybe it will not always work since it isn't the proper way to do it?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    I'm glad you enjoyd Italy!

    Ok, let's suppose you have:

    1994-01-01
    1994-01-02
    1994-01-03

    Since you are GROUPing by year and month, all these dates fall inside the same group. So, which one should be selected by MySQL? It chooses one randomly. This behavior may be ok for you, and I think it works fine as long as you use MySQL. However it is not logically correct and is not standard. That's why I suggested you to change your query

    If you need to change indexes... just do it All your queries will still work. Just check the EXPLAIN output.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,799
    Rep Power
    529
    Thanks again f_razzoli,

    I think I agree with you and will change my query. While it currently works perfectly, whenever I do something that is not standard, it ends up biting me.

IMN logo majestic logo threadwatch logo seochat tools logo