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

From: toby <>
Date: Wed, 28 Jan 2009 13:57:46 -0800 (PST)
Message-ID: <>

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 - 15:57:46 CST

Original text of this message