help needed in getting an average and displaying it

I hope some guru can set me straight

[PRosales]

I have the following data set

'id' 'cluster_name' 'insert_dt_tm'    'nodes_available' 
'1'    'Atlantis'    '2/1/2005 00:03:00'   '45' 
2     Atlantis      2/1/2005 01:03:00     45 
3     Atlantis      2/1/2005 02:03:00     45 
4     Atlantis      2/1/2005 03:03:00     45 
5     Atlantis      2/1/2005 04:03:00     45 
6     Atlantis      2/1/2005 05:03:00     45 

I am trying to create an average for nodes available for the day and have the output to resemble

Atlantis 2/1/2005 60
Atlantis 2/2/2005 60

I have the basics down for PHP but need some guidance on this one.

Any help is greatly appreciated.

Pete

[edited by: ergophobe at 9:34 pm (utc) on April 8, 2005]
[edit reason] Data reformatted and snipped [/edit]

[ergophobe]

Welcome PRosales,

I reformatted the data to make it easier to read, but couldn't reformat all of it.

How is this being stored (text file, database, etc?). Is this just a log file of some sort that you want to parse? In which case is it fair to assume that it is tab-delimited?

[deizu]

Assuming this is a mysql database, the following query should be what you're looking for

SELECT cluster_name, 
  DATE_FORMAT(insert_dt_tm, '%Y-%m-%d') as day,
  AVG(nodes_available)
FROM table_name
GROUP BY cluster_name, day

[PieSocial]

^^ *edit: oh his solution is better.

could you not explode() the dates, and then work out how to do a mathmatical average from there?

[PRosales]

ergophobe and deizu,

Let me begin by thanking you both for replying. I truly appreciate the assitance.

This is a MySQL database that is being fed the data by several computer clusters. The data begins as text files that is being parsed and inserted into the database.

I will give a go with your recommendation deizu and hopefully get the results I am trying to achieve.

I believe there is a way to capture the timestamp in various formats. Should it be changed to make querying against it easier?

Pete

[PRosales]

I am getting the following error:

You have an error in your SQL syntax near 'as day,
AVG(nodes_available)
GROUP BY cluster_name, day LIMIT 0, 30' at line 1

Any suggestions?

[coopster]

You'll have to take a look at the syntax of the rest of your query statement, something isn't quite right. If you are building and executing the statement with PHP, try dumping the statement to the browser to check the syntax, especially if you are inserting any variable data.

[PRosales]

Excellent advice coopster.

I was testing the query through phpMyAdmin and was given the error.

Once I implemented the query through a PHP page, the desired results was achieved.

<?php
// Make a MySQL Connection
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

// Retrieve all the data
$result = mysql_query("SELECT cluster_name,
DATE_FORMAT(insert_dt_tm, '%Y-%m-%d') as day,
AVG(nodes_available) as nodes_average
FROM Nodes_Available_February_2005
GROUP BY cluster_name, day")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Day</th> <th>Average</th> </tr>";
// store the record of the "example" table into $row
while($row = mysql_fetch_array( $result )) {
// Print out the contents of the entry
echo "</td><td>";
echo "".$row['cluster_name'];
echo "</td><td>";
echo "".$row['day'];
echo "</td><td>";
echo "".$row['nodes_average'];
echo "</td></tr>";
}
echo "</table>";
?>

Thanks to all who aided, especially deizu for constructing the query for me. I am certain I will need some more assistance in the very near future.

Pete