CODEWITHSUNDEEP
mysqlgroup-by
Ben Wilson
Ben Wilson

Reputation: 2676

Group by week returning strange intervals

For some odd reason, group by week is returning odd date intervals with a datetime field.

"Completed" is a datetime field, and using this query:

SELECT 
    Completed, 
    COUNT( DISTINCT Table1.ID ) AS ActivityCount 
FROM Table1 
JOIN Table1Items 
ON Table1.ID = Table1Items.ID 
JOIN database_database.Table2 
ON Table2.Item = Table1Items.Item 
WHERE Completed != '0000-00-00' AND Completed >= '2012-09-25' AND Completed <= '2012-10-25' 
GROUP BY WEEK(Completed)

I'm getting:

Completed  ActivityCount    CompletedTimestamp
2012-09-25  300            2012-09-25 00:00:00
2012-10-02  764            2012-10-02 00:00:00
2012-10-08  379            2012-10-08 00:00:00
2012-10-17  659            2012-10-17 00:00:00
2012-10-22  382            2012-10-22 00:00:00

some are 7 days apart, others are 6 days apart, others are 5.... and one is 9?

Why does it group the dates by such strange intervals instead of just 7 days?

Upvotes: 0

Views: 154

Answers (2)

Ben Wilson
Ben Wilson

Reputation: 2676

The answer, as alluded to by juergen d, was to aggregate the date column -- use min or max depending on whether you want to the first day or last day of the week used as the consistent interval; e.g.:

SELECT MIN(Completed), COUNT( DISTINCT Table1.ID ) AS ActivityCount FROM Table1 JOIN Table1Items ON Table1.ID = Table1Items.ID JOIN database_database.Table2 ON Table2.Item = Table1Items.Item WHERE Completed != '0000-00-00' AND Completed >= '2012-09-25' AND Completed <= '2012-10-25' GROUP BY WEEK( Completed)

Upvotes: 0

juergen d
juergen d

Reputation: 204766

The week function does not count the difference of the dates.

The week function returns the week number of a date. If you group by it, then in the group will be dates at the start and end of the week and in bettween. The difference betwween the single dates can be greater than 7 days or less.

Upvotes: 1

Related Questions