List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:April 29 2010 1:05pm
Subject:RE: Obtain week number between years
View as plain text  
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.schwartz@stripped [mailto:baron.schwartz@stripped] 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.tompkins@stripped> 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=1
> 
> 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

Thread
Obtain week number between yearsTompkins Neil29 Apr
  • Re: Obtain week number between yearsJohan De Meersman29 Apr
  • Re: Obtain week number between yearsBaron Schwartz29 Apr
    • RE: Obtain week number between yearsSteven Staples29 Apr