arithmetic mean per day between two dates

Question

I'm currently fetching all the values from my table, between two dates. The values per day are not always the same and i need to do an arithmetic mean (not sure if this is the correct term) per days, of the values between the two dates.

for example:

this are some of the elements of my table

['lig']   ['date']
170     2016-05-26
190     2016-05-26
192     2016-05-26
196     2016-05-25
181     2016-05-25
187     2016-05-25
195     2016-05-25

I need to do some thing like this:

mean day 26: 170+190+192/3= 184
mean day 25: 196+181+187+195/4 = 189,75

this is how i'm fetching all the values between dates.

function listDates($table, $beginDate, $endDate){ 
    $select = "SELECT * FROM $tabela WHERE date >= :beginDate AND date <= :endDate ORDER BY date"; 
    $inst = $this->liga->prepare($select);
            $inst->bindParam(':beginDate', $begin);
            $inst->bindParam(':endDate', $end);
    $inst->execute();
    return $inst->fetchAll();             

}

this is how i get the interval of dates i want and list the values between that interval

$previous_week = strtotime("-1 week");
$today = strtotime("-1 day");

$start_week = strtotime("today", $previous_week);
$end_week = strtotime ("next day", $today);

$start_week = date("Y-m-d H:i:s", $start_week);
$end_week = date("Y-m-d H:i:s", $end_week);

$values = $listValues->listDates("meanVal",$start_week,$end_week);

        foreach($values as $val){

           echo $val['lig']; 
           echo $val['tip']; 
           echo $val['hum']; 
           echo $val['date'];     
} 

But how can i make a count(?) to get mean values per day? Anybody could help me? Not sure if this was explicit, english is not my first language and sometimes i don't know how to explain some stuff. Thanks a lot.

Answer 1

If you cannot change the query mentioned in other answer, here is the php code to get the desired result.

1. create a new array with the keys as date.
2. calculate mean value for each date.

php code

foreach($values as $val){
  // generates array with date as key
  $dates[$val['date']][] = $val['lig']; 
}

foreach($dates as $k=>$v){
     // calculates mean value for each date
     $count = count($v); 
     $sum = array_sum($v); 
     $mean = $sum / $count;  
     echo $k.":".$mean;
}

Out put:

2016-05-26:184
2016-05-25:189.75

Answer 2

I'll give you the sql and let you figure out the php part yourself :

select *,avg(lig) as day_mean 
from table
where date >= begindate and date <= enddate
group by date
order by date

the key here is the group by clause, group by will apply aggregation functions(it's avg here) for every distinct value of what comes after it(date).

Group by can group on more than one field grouping by the first field and on equal values groups by the second and on equal etc.

There are many aggregation functions that can be used with group by like avg,sum,max,count,min,etc.

A couple of notes here :
1- I left the * because I thought you need the other data, if you only need the average simply remove it.
2- the as keyword in the previous code is used to name the average(otherwise its name would be avg(lig)) , this is for better naming in the php part but it serves another purpose : what if you wanted only the average bigger than X ? or whatever constraint on it ? then you achieve this by adding having day_mean>X to the end of the previous code(you can put after having what you can put after where but it's only used for aggregation columns, where can't be used for those columns).
3- the where clause is applied before grouping, this mean mysql will first select the rows that satisfy the where clause and then it groups them, this is very important because this way the aggregate function is only applied on the selected rows not on the entire table.

Answer 3

Not sure what you need code wise, but this query produces the same values as your example:

$ mysql -e 'desc foo'
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| lig   | int(11) | NO   |     | NULL    |       |
| date  | date    | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+


$ mysql -e  'select sum(lig) as sum, 
             count(*) as ligs, sum(lig)  / count(*) as average
             from foo group by date ' 
+------+------+----------+
| sum  | ligs | average  |
+------+------+----------+
|  759 |    4 | 189.7500 |
|  552 |    3 | 184.0000 |
+------+------+----------+