GROUP BY date question

Dev Shed Forums Sponsor:

January 18th, 2013, 09:59 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 9 sec
Reputation Power: 526

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)

January 19th, 2013, 09:36 AM

f_razzoli

Contributing User

Join Date: Jan 2013

Location: Italy

Posts: 36

Time spent in forums: 4 h 37 m 48 sec
Reputation Power: 1

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

January 19th, 2013, 09:58 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 9 sec
Reputation Power: 526

Hello f_razzoli from Italy! I was just there several months ago and had a wonderful time.

Thank you for your reply.

Quote:

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?

January 19th, 2013, 10:15 AM

f_razzoli

Contributing User

Join Date: Jan 2013

Location: Italy

Posts: 36

Time spent in forums: 4 h 37 m 48 sec
Reputation Power: 1

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.

January 19th, 2013, 01:07 PM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 9 sec
Reputation Power: 526

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.

GROUP BY date question

Developer Shed Advertisers and Affiliates