Getting average by week
Moderator: General Moderators
Getting average by week
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.
Re: Getting average by week
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.
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);
Re: Getting average by week
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!