Getting average by week

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
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Getting average by week

Post by jraede »

I have a table of tasks, with a date column called `date_completed`. I want to find the average # of tasks completed per week, without counting any weeks prior to the first task completed, and obviously not counting weeks after the current week. I know this involves AVG() and probably some sort of GROUP BY syntax, but this is far beyond my knowledge of SQL. Thanks in advance.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Getting average by week

Post by jraede »

Update:

I have this, does this look right? It's hard to know without having a bunch of real tasks instead of adding/completing them for testing purposes in the development phase.

Code: Select all

$query = mysql_query("SELECT COUNT(`li_id`) FROM `list_items` LEFT JOIN `lists` ON `list_items`.`li_list` = `lists`.`list_id` WHERE `li_completed`='1' AND `list_type`='$type' GROUP BY WEEK(`li_completed_date`), YEAR(`li_completed_date`)") or die(mysql_error());
while($row = mysql_fetch_row($query)) {
    $weeks[] = $row[0];
}

$stats_array['completed_per_week'] = array_sum($weeks)/count($weeks);
 
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Getting average by week

Post by jraede »

Another update: this works, except the count doesn't consider weeks with no completed tasks. So I guess that's the only part I'm having trouble with. Any help would be appreciated. Thanks!
Post Reply