Selecting Weekly Averages from MySQL table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Klaus2003
Forum Newbie
Posts: 2
Joined: Sun Dec 21, 2003 11:11 am

Selecting Weekly Averages from MySQL table

Post by Klaus2003 »

Hi Guys,

I need some help for a piece of PHP code.

I have a MySQL table (let's call it myweight) where I have the following columns: userid, signupdate, weight, currentdate.

What I need is a code that would calculate the weekly average weight of each user based on the signup date. I know I how to calculate the average weight for a user using the AVG() function in MySQL, but I'm stuck with finding a way to select, for each week since somebody has been a member, only the weight values from that week.

Thanks a lot for your help. :D

Klaus.
User avatar
Derfel Cadarn
Forum Contributor
Posts: 193
Joined: Thu Jul 17, 2003 12:02 pm
Location: Berlin, Germany

Post by Derfel Cadarn »

I assume each user has a weight-value entered each week. In that case the SQL-query would be:

Code: Select all

$userid = $_POSTї'$userid']; // I assume you get the userid from a loginform
SELECT AVG(weight) FROM myweight WHERE userid=$userid
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

Well there is 604800 seconds in a week. So you could select (supposing you're date is a timestamp) between the timestamp they signed up and 604800. And then just make it in intervals of that.
Klaus2003
Forum Newbie
Posts: 2
Joined: Sun Dec 21, 2003 11:11 am

Selecting Weekly Averages from MySQL table

Post by Klaus2003 »

Thanks guys!

I like your idea, Pyrite. Could you be more specific as to how the select with the timestamp difference could be written? I am a beginner, so please bear with me.

The reason I need these weight averages is to plot them on a graph.

Thanks again.

Klaus
Post Reply