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

From: toby <toby_at_telegraphics.com.au>
Date: Wed, 28 Jan 2009 13:57:46 -0800 (PST)
Message-ID: <cba86e0d-343c-468e-9e3c-23d4c3147f1c_at_i20g2000prf.googlegroups.com>


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