[RESOLVED] Trying to average data per day (not per entry)

[Francoise]

I have a very simple online program that permits a user to enter data several times a day. I'd like to permit the user to look at his or her own data in some new ways.

The actual data is very personal to the user so I'll use outdoor temperature readings as a way to frame my question. I'm using PHP 5 and MySQL.

Suppose user George has provided the following entries:

April 1; 9:05 a.m.; 60 degrees;

April 2; 6:10 a.m.; 55 degrees;
April 2; 9:00 a.m.; 68 degrees;
April 2; 10:15 a.m.; 72 degrees;

April 5; 8:58 a.m.; 48 degrees;
April 10; 9:02 a.m.; 70 degrees.

April 14; 8:55 a.m.; 62 degrees.

I'd like for the user to be able to get an average reading for the hours from 6 a.m. to 12 noon during the period April 1 to April 14.

I can probably write a query that would enable George to do that except that I'm not sure how to deal with the multiple readings for April 2nd. I'd want to average the 3 readings for April 2nd, which would yield a value of 65; and then use 65 as the 'entry' for that day when averaging across the entire two-week period.

My original idea was to use a PHP conditional to identify which days have multiple entries, and address those in an if conditional; and then deal with single entry days in the else clause. And then I'm not sure -- assign the values to an array and average them in that form? However, I'm told that that is an awkward and inefficient approach and have been generally discouraged from trying it. At the moment, I'm left with no ideas at all.

I'm doing this partly as a learning exercise and so I'm not asking anyone to write the code. Can someone give me an idea of where to start? If you can give me some of the vocabulary, I'll research it myself and see if I can come up with something.

I'm hoping for a simple solution that I'll be able to understand and implement. Right now an inefficient solution that I can use will be better than an efficient one that is too sophisticated for me and that I can't use.

Many thanks for any help you can give me with this.

[Francoise]

P.S. I'm told that I should try to do the averaging within the actual SQL query. That's a bit of a leap for me as I've only ever written about 2 SQL queries and they were both extremely simple. If that really is the better way to go, though, I'll try it. I'm just asking for some guidance at present. Thank you again.

[NogDog]

You already know what I think. 😉

[Francoise]

Yes, I know what you think. But I'm only slightly wiser.

[Francoise]

Hello, to any kind person who might be willing to help me with this --

in researching this via Google, I've come across the term subquery (wrt SQL queries). Is that what I need to be looking at? That is, my main query would use AVG (), but there would be a subquery for any day WHERE there is more than one entry, and that subquery would take an AVG() for that specific day?

.

[NogDog]

A "GROUP BY" should be all that's necessary, along with the AVG() function in SQL.

http://www.webdeveloper.com/forum/showthread.php?276917-How-to-take-the-average-of-several-sets-of-numbers-%28assigning-variable-names-%29&p=1263755#post1263755

[Francoise]

It just seems to me that the date on which there is more than one entry has to be treated differently than the other dates.

If I've already told you, repeatedly, that I do not understand your answer, why do you not accept that? I have now come to two forums in the hope of actual help, only to be disappointed in both places.

Perhaps someone else will be willing to reply. Someone who wants me to be able to understand the answer and benefit from it.

[NogDog]

That's what the GROUP BY clause in the SQL allows you to do: in this case it will perform the AVG() function on all temperature readings for a day, since we are grouping by the date result. So instead of returning one query result row per temperature reading, you instead get one result row per day, with all applicable readings for that day being averaged together. (The actual query may vary a bit depending upon how exactly you have your DB table(s) structured -- and named, of course -- but the general approach should remain the same.)

[Francoise]

Okay, you win. I'm not sure what you've won, nor why you wanted it, but you've won. I'll mark this "Resolved," and try to find a more constructive way of moving forward.

[Francoise]

This question has not been resolved -- far from it. Under the circumstances, however, I will give up for the present. In a few days I will either re-phrase the question and try again here, or go elsewhere. I'm sorry this couldn't have been a more fruitful exercise.

[NogDog]

<html>
	<head>
		<title>test</title>
		<style type='text/css'>
			table {
				border-collapse:collapse;
			}
			th,td {
				border:solid 1px black;
				padding: .2em .4em
			}
		</style>
	</head>
	<body>
<?php
$db = new mysqli('localhost', 'root', null, 'test');
// see what the test data looks like:
$result = $db->query("SELECT * FROM temp_reading ORDER BY temp_reading_id");
echo "<h3>Test Data</h3>\n";
echo "<table>\n";
while(($row = $result->fetch_assoc()) != false) {
	printf(
		"<tr><td>%d</td><td>%s</td><td>%d</td></tr>\n",
		$row['temp_reading_id'],
		$row['reading_date_time'],
		$row['temp_f']
	);
}
echo "</table>\n";

// pick the dates/times we want to check
$startDate = '2013-04-01 00:00:00';
$endDate   = '2013-04-04 23:59:59';
$startTime = '06:00:00';
$endTime   = '12:00:00';

// get down to business...
$sql = "
	SELECT
		DATE(reading_date_time) AS reading_date,
		AVG(temp_f) AS avg_temp
	FROM temp_reading
	WHERE
		reading_date_time BETWEEN ? AND ?
	AND
		TIME(reading_date_time) BETWEEN ? AND ?
	GROUP BY reading_date
	ORDER BY reading_date ASC
";
$stmt = $db->prepare($sql);
if($stmt == false) {
	die($db->error);
}
$stmt->bind_param('ssss', $startDate, $endDate, $startTime, $endTime);
if($stmt->execute() == false) {
	die($stmt->error);
}
$result = $stmt->get_result();

// show result:
echo "<h3>Test Results</h3>\n";
echo "<table>\n<tr><th>Date</th><th>Temp</th></tr>\n";
while(($row = $result->fetch_assoc()) != false) {
	echo "<tr><td>".$row['reading_date']."</td><td>".round($row['avg_temp'],1)."</td></tr>\n";
}
echo "</table>\n";
?>
	</body>
</html>

Output:
[ATTACH]4883[/ATTACH]

temp_test.png