Page 1 of 1

Averaging data for each day

Posted: Mon Nov 29, 2004 11:14 am
by enfys
How can I work out the average of the following:

Users enter in various figures into a MySQL database (the type is unimportant other than they are integers) at various times of the day. The number of figures entered for each day could vary from none to 24.

so for example, a user could have entered the following data;

27 Nov 10:00 - 120
27 Nov 13:00 - 130
27 Nov 16:00 - 125
27 Nov 22:00 - 135
28 Nov 06:00 - 140
28 Nov 20:00 - 115
28 Nov 23:00 - 135
30 Nov 18:00 - 160

How can I query the database selecting all the data entered but then averaging the figures for each day.

Using the example above I would want to show the following:

Average figures:
27 Nov = 130
28 Nov = 130
30 Nov = 160

I'm ok with using array_sum() and count() to average the figures but can't figure out how to group them into days.

Thanks in advance

Posted: Mon Nov 29, 2004 11:24 am
by kettle_drum
Messy hack:

Code: Select all

...WHERE date LIKE '27 NOV%'
Or change the date values to seconds since the unix epoch and then do:

Code: Select all

$start = mkdate(); //put in the data you want
$end = $start+(60*60*24);

$result = mysql_query("SELECT sum(nums) FROM blah WHERE date BETWEEN '$start' AND '$end'");

Posted: Mon Nov 29, 2004 11:30 am
by Maugrim_The_Reaper
Store date only - 28 Nov - use date() to format the current timestamp at data entry.

Code: Select all

<?php
$query = mysql_query("select distinct(date) from MYTABLE");
while($thisrow = mysql_fetch_array($query)){
    $newq = mysql_query("select avg(data) from MYTABLE where date = '$thisrow[date]'");
    $newrow = mysql_fetch_array($newq);
    echo($thisrow[date]."->".$newrow[data]);
}
?>
Would work for limited data - but it's a lot of queries as your number of dates increase...so it will get very very messy if you're storing a lot of dates to list.

I think I got the mysql functions right...:) I'm more used to using an abstraction class...

Posted: Mon Nov 29, 2004 12:11 pm
by swdev
This can be done in a single MySQL query. Ths code is untested.

Code: Select all

$query = 'SELECT date_col, AVG(data_col) as average
               FROM table_name
               GROUP BY date_col
               ORDER BY date_col ASC';

$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
  echo 'Date = ' . $row['date_col'] . ' average = ' . $row['average'];
}
replacing
date_col with the name of your column that holds the date field
data_cll with the names of your column that holds the data that yu want to average
table_name with the name of the table that holds the data

Hope this helps

Posted: Mon Nov 29, 2004 12:20 pm
by xisle
store a timestamp for greater flexibility later

Code: Select all

SELECT AVG(fieldname) as avg, DATE_FORMAT(timefield, '%e %b') as mydate from tablename 
GROUP BY mydate
do it with one query as well..