Re: How can a group by clause be modified so the end weeks of the year won't be split?

Ted Byers wrote:
> I have a SELECT statement similar to the following:
>
> SELECT YEAR(transaction_date) AS y,WEEK(transaction_date) AS w, COUNT
> (*) AS c FROM transaction_data_view WHERE mid = 300
> GROUP BY YEAR(transaction_date),WEEK(transaction_date)
> ORDER BY YEAR(transaction_date),WEEK(transaction_date);
>
> The ONLY problem, here, is that if New Years day occurs in the middle
> of the week, that week's data will be split at the first second of the
> New Year. Of course, my real select statement is much more complex
> than this, involving joins of both tables and views (and it is quick),
> but this suffices to make the only remaining problem obvious.

Try GROUP BY TO_DAYS(transaction_date+x) DIV 7 Where x is a correction to give you the correct week starting day.

>
> If it matters, this is being done in MySQL.
>
> So, how, then, can I fix the GROUP BY and ORDER BY clauses?
>
> Thanks,
>
> Ted
Received on Wed Jan 28 2009 - 22:57:46 CET