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

On Jan 28, 4:57 pm, toby <t..._at_telegraphics.com.au> wrote:
> 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

I mean, 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:58:27 CET