January 18th, 2013, 10:59 AM
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(*)?
SELECT COUNT(*) AS total, myDate FROM myTable GROUP BY YEAR(myDate), MONTH(myDate)
January 19th, 2013, 10:36 AM
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:
Also, if you have values like NULL or '0000-00-00' in myDate, maybe you want to exclude them with:
SELECT COUNT(*), YEAR(myDate), MONTH(myDate) ...
January 19th, 2013, 10:58 AM
Hello f_razzoli from Italy! I was just there several months ago and had a wonderful time.
Thank you for your reply.
Why not? What would happen?
Let's say I run the following query:
Possible returned data will be something like:
SELECT COUNT(*), myDate, YEAR(myDate), MONTH(myDate)...
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).
213 2012-01-01 2012 01
413 2012-02-03 2012 02
242 2012-03-02 2012 03
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?
January 19th, 2013, 11:15 AM
I'm glad you enjoyd Italy!
Ok, let's suppose you have:
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.
January 19th, 2013, 02:07 PM
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.