How to group data to show average price by weeks from a select query

To do the whole process in PHP change your select and remove the select_avg and do a normal select of unitPrice.

$new_result_array=[];$average_prices=[];foreach($result_array as $row){     //Initialize the index's if they dont exist   if(!isset($new_result_array[date('W',strtotime($row['createdDate']))]){     $new_result_array[date('W',strtotime($row['createdDate'])]=[];     $new_result_array[date('W',strtotime($row['createdDate'])]['weekly_order_total']=0;     $new_result_array[date('W',strtotime($row['createdDate'])]['orders']=[];   }   //Edited to wrap $row['createdDate'] in strtotime() as all dates are returning week 1    $new_result_array[date('W',strtotime($row['createdDate']))]['weekly_order_total']+=$row['unitPrice'];   $new_result_array[date('W',strtotime($row['createdDate']))]['orders'][]=$row;}print_r($new_result_array);foreach($new_result_array as $week=>$orders){   $average_prices[$week]=$orders['weekly_order_total']/count($orders['orders']);}print_r($average_prices);

W as a date format will give you the number of the weeek (ie 1-52). If you need to show the start and end date of each weekly period you will need to parse that number. See this question (and probably others) for more info

PHP get start and end date of a week by weeknumber

Not a portable solution (it uses a MySQL function; other RDBMs have similar features but differently named), but you can group the data by WEEKOFYEAR(). The only caveat is that this way if you're in January, you will get e.g. weeks #48, #49, #50, #51, #52, #1, #2, #3 and you will need to sort them just like that (i.e. 52 comes before 1).

So you can extract

MIN(ordersheader.createdDate) AS firstOrderDate,WEEKOFYEAR(ordersheader.createdDate) AS weekNumber,

and then group by weekNumber but order by firstOrderDate (without displaying it).

Another problem might be if you have missing data in the interval.

Perhaps a more high-level approach would be:

1. Extract day average prices. You then get from 0 to ~91 items worth of data points (0 if you have no orders in the period; you have to consider the possibility).
2. Use some extending method (B-splines [easy on the eyes], polynomial [risky], linear average [defensible]) to get data for the days you do not have; this to be done in PHP
3. Re-group the data in PHP, from 90 days to 12 weeks.
4. Display the 12 data points.

Grouping the data

You can add missing data in the query but it's tricky. A more approachable way from PHP would be this, supposing that you have a result array like

$results = array(    0 => array(        'createdDate'   => '2015-07-17',        'value'         => 2500, // This is the DAY AVERAGE for that day        ...    ),

Then you can do something like,

$weeks = array();foreach ($results as $row) {    $weekNumber = date('W', $row['createdDate']);    if (array_key_exists($weekNumber, $weeks)) {        $week = $weeks[$weekNumber];    } else {        $week = array( 'value' => 0, 'count' => 0 );    }    $week['count'] ++;    $week['value'] += $row['value'];}// Now get the averageforeach ($weeks as $n => $week) {   $weeks[$n]['value'] /= $weeks[$n]['count'];}// Now we have $weeks, with "holes" for periods with no data:/*    $weeks = array(        51 => [ 'value' => 172.371, 'count' => 3 ],         2 => [ 'value' => 211.952, 'count' => 7 ],         ...    );*/// To fill holes, we first need to choose how to do that (see above).

Note: there is a (possibly acceptable) conceptual error in the above approach, where we average the day averages. Suppose we have:

Day      Price17       100017       100017       100018       2000

The day averages will be: 17 = 1000, 18 = 2000. If both days are in the same week, the average of 1000 and 2000 will give a week average of 1500. Yet in that week we had four orders, and the average of those would actually be 1250.

There is an ambiguity when you say "week average", because it is not clear whether it is the average of the prices of each day, or the average of the prices of all orders in the whole period.

If you want the second type of average, you need to group by week number, which was my initial proposal above.