[SOLVED] Returning results by week

[soycharliente]

I am trying to figure out how to get data on a per week scale. The fields in the table that matter would probably be:

`id` int( NOT NULL auto_increment
`pid` int(4) NOT NULL default '0'
`thedate` date NOT NULL default '0000-00-00'

 

pid is the parent id. (I have another table with locations and corresponding ids.) I would like to count the unique pids (I've used GROUP BY before) in each week since the first entry. I really have no idea where to start. I don't even know if this is possible.

 

I'm not asking for full code. Some sudo-code would be great. I've been thinking about it for a while and don't even know where to begin. Can someone give me a hint in the write direction so I can begin to write some code and come back if it breaks?

 

 

[PFMaBiSmAd]

Untested but should probably get you started -

 

SELECT pid, count(*) as cnt, YEARWEEK(thedate) as yw FROM your_table GROUP BY yw, pid

 

or -

 

SELECT pid, count(*) as cnt, YEARWEEK(thedate) as yw FROM your_table GROUP BY YEARWEEK(thedate), pid

[corbin]

Edit:  Beaten to it....  Gotta stop opening 10 tabs....

 

 

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

 

WEEKOFYEAR would be what you're looking for.

 

If you plan on doing this on a ton of records, you might want to precalculate the week of the year since it could get expensive, especially if using the field as a constraint or in a group by, which you will be doing.

[soycharliente]

Right now my date range is 15 August 2005 - 22 January 2009.

[soycharliente]

SELECT COUNT( * ) AS count, YEARWEEK( thedate ) AS yw
FROM lunch_data
GROUP BY yw

 

@PFMaBiSmAd: This seems to be exactly what I want. I thought it would be may more complicated.

 

@corbin: Thanks for the link. That helps for other things I'm trying to do as well.