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
Averaging data for each day
Moderator: General Moderators
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
Messy hack:
Or change the date values to seconds since the unix epoch and then do:
Code: Select all
...WHERE date LIKE '27 NOV%'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'");- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
Store date only - 28 Nov - use date() to format the current timestamp at data entry.
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...
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]);
}
?>I think I got the mysql functions right...
This can be done in a single MySQL query. Ths code is untested.
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
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'];
}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
store a timestamp for greater flexibility later
do it with one query as well..
Code: Select all
SELECT AVG(fieldname) as avg, DATE_FORMAT(timefield, '%e %b') as mydate from tablename
GROUP BY mydate