Average

[jacrewq]

I have a table of values like

Bob 7
Tom 6
Tom 100
Bob 200

And I want to select the average of the numbers. But only of the first number for each name:
So I would want the average only of
Bob 7
Tom 6

How do I do that?

[goa103]

Hi,

The solution is to use a temp table to store the mins, then to get the average value from these mins:

CREATE TEMPORARY TABLE NameMinNum SELECT Name, MIN(Num) AS Num FROM threadid_10238795 GROUP BY Name;
SELECT AVG(Num) FROM NameMinNum;

You can't MIN then AVG because of the GROUP BY on Name. It would be quicker but not logical 🙂

JM

[jacrewq]

This is my sql:

$query = "CREATE TEMPORARY TABLE NameMinNum SELECT MIN(speed) AS Num FROM readings GROUP BY name; SELECT AVG(Num) FROM NameMinNum; ";

readings is the table's name
speed clumn contains the numbers

But I got this error:

1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT AVG(Num) FROM NameMinNum' at line 1

Whats the problem?

[tomhath]

What makes a row "first". Do you mean the lowest number? Or is there some other sort criteria?

If you only want the lowest, what goa103 posted will work from the command line, but you'll probably have to do it with two mysql_query statements in php.

[jacrewq]

I mean the row that was inserted earlier

[goa103]

$query = "CREATE TEMPORARY TABLE NameMinNum SELECT MIN(speed) AS Num FROM readings GROUP BY name; SELECT AVG(Num) FROM NameMinNum; ";

You can't execute 2 queries at the same time:

$TempQuery = "CREATE TEMP...";
mysql_query ($TempQuery);

$AvgQuery = ""SELECT AVG...;
mysql_query ($AvgQuery);

1 query after an other. You should read the good introduction to SQL from the MySQL manual !

JM

[laserlight]

Well, a more PHP-oriented alternative would be to use:

$query = "SELECT DISTINCT speed FROM readings ORDER BY id";

Where id would be the primary index of the readings table.
After that you can place the result returned in an array and find minimum, arithmetic mean, median or both, as required.

[jacrewq]

query = "SELECT DISTINCT speed FROM readings ORDER BY id"; gave me an array of numbers. how can I find their average?

P.S. the two query method returned a huge negative number..

[Sxooter]

WARNING: Just because it's the first row returned doesn't mean it was inserted first. The SQL spec makes it clear that a SQL engine doesn't have to return the dataset in any particular order unless you have an order by.

So, what you technically need to do if you'd like to make your SQL code more resiliant and not rely on your database to give you the rows in the order they were inserted, is to create a date/timestamp field used to mark when the row is updated. Then, order by that field.

[laserlight]

Actually, if an AUTO_INCREMENT index field (i.e. my suggested 'id' field) is used, then one will not need to record a timestamp, since the id will provide the same functionality.

To find the average in an array of numbers, $num_array,
Arithmetic mean:

$mean = array_sum($num_array) / count($num_array);

though you might have to check that the array has values, or you will get a division by 0 runtime error.

Median:

sort($num_array);
$num = count($num_array);
if ($num % 2 == 0) {
	//array has even number of values
	$num /= 2;
	$median = ($num_array[$num] + $num_array[$num-1]) / 2;
}
else {
	//array has odd number of values
	$num = ($num - 1) / 2;
	$median = $num_array[$num];
}