Averaging data for each day

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
enfys
Forum Newbie
Posts: 4
Joined: Thu Nov 18, 2004 4:32 am

Averaging data for each day

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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'");
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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...
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post 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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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..
Post Reply