You could also try it this way, and then in the application, you can find
out what the dates are, that they are between.
SELECT YEARWEEK(`datefield`) AS 'week',
COUNT(`visits`) AS 'visits'
FROM `mytable` WHERE YEAR(`datefield`) = '2009'
GROUP BY YEARWEEK(`datefield`);
(this was just taken off the top of my head, but it should give something
that you're looking for... hopefully ;) )
Steven Staples
> -----Original Message-----
> From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On
> Behalf Of Baron Schwartz
> Sent: April 29, 2010 8:34 AM
> To: MySql
> Subject: Re: Obtain week number between years
>
> Neil,
>
> I would start with something like this, assuming the date column is
> called "d":
>
> SELECT <count, or sum, or whatever aggregate function>
> FROM <table>
> GROUP BY d - INTERVAL DAYOFWEEK(d) DAY;
>
> - Baron
>
> On Thu, Apr 29, 2010 at 8:12 AM, Tompkins Neil
> <neil.tompk...@googlemail.com> wrote:
> > Hi
> >
> > We need to produce a query to return the total number of user visits
> between
> > two date ranges that span over two year e.g from 2009-04-29 to 2010-
> 04-29.
> > My question is how can I compute the totals for each week within a
> query ?
> > for example
> >
> > 2009-04-29 to 2009-05-06 100 visits
> > 2009-05-07 to 2009-05-14 250 visits etc
> >
> > Cheers
> > Neil
> >
>
>
>
> --
> Baron Schwartz
> Percona Inc <http://www.percona.com/>
> Consulting, Training, Support & Services for MySQL
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date:
> 04/29/10 02:27:00
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
