Adding Up Numbers in Mysql Table

[gladiator83x]

Hi All,

I was just wondering if someone could assist me in adding up a column full of numbers in my mysql table? I am trying to compute the average. This is what I started off with--but it doesnt return the sum. My table's name is More_Metrics. The column within my table is named Days.

$query= "SELECT More_Metrics.Days FROM More_Metrics WHERE More_Metrics.Topic_Title='$title'";
$result1 = mysql_query($query) or die('Query failed: ' . mysql_error());
$i=0;
while ($line = mysql_fetch_array($result1, MYSQL_ASSOC)) {
echo "<tr>\n";


  foreach ($line as $col_value1) {
//   echo "\t\t<td>$col_value1</td>\n";

$i=$col_value1 +$i;
$j=$i;///then i want to try to return $j--but i get nothing
}
}

I guess I am a little confused.

[cmgmyr]

If you just want to output an average there is AVG() for MySQL.

[leeming]

"SELECT AVG(More_Metrics.Days) FROM More_Metrics WHERE More_Metrics.Topic_Title='$title'"

kinda like that

[cmgmyr]

there ya go  :)

[gladiator83x]

Thanks a ton!!!      :)

[gladiator83x]

I thought that I had it...but I didn't.

$query = mysql_query("SELECT avg(Days) AS columnaverage FROM More_Metrics WHERE More_Metrics.Topic_Title='$title'");
$array = mysql_fetch_result($query);
$totalaverage = $array[columnaverage];
echo "$totalaverage";

I received the message:
Fatal error: Call to undefined function mysql_fetch_result() in /export/home2/webpages/more_metrics.php on line 422

The error before that didnt recognize the AVG() fucntion. What am I doing wrong? It seems correct to me.

[wildteen88]

Use mysql_fetch_assoc

mysql_fetch_result doesnt exist.

[gladiator83x]

Okay--I switched to mysql_fetch_assoc but it only return the last number in the column. I wrote the code 2 ways and I received the same result. Any suggestions?

1:
$query = mysql_query("SELECT avg(More_Metrics.Days) AS columnaverage FROM More_Metrics WHERE More_Metrics.Topic_Title='$title'");
$array = mysql_fetch_assoc($query);
$totalaverage = $array[columnaverage];
echo "$totalaverage";


2:
$sql= "SELECT AVG(More_Metrics.Days) FROM More_Metrics WHERE More_Metrics.Topic_Title='$title'";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
$line = mysql_fetch_array($result, MYSQL_ASSOC);
foreach ($line as $col_value) {
    echo "\t\t<td>$col_value</td>\n";
}

[king arthur]

There will only be one value in the array - the average of all the rows for that column.

[ryanlwh]

i think the average equals to the last record so it appears that you're getting the last one.

[gladiator83x]

no i'm really just returning the last cell in my column--which doesn't come close to what the avg should really be-- i'm just confused. is there any other way to do this?

[Barand]

[code]<?php
$query = mysql_query("SELECT AVG(Days)  FROM More_Metrics
              WHERE Topic_Title='$title'" );
echo mysql_result($query, 0, 0);
?>
[/code]

[king arthur]

[quote author=gladiator83x link=topic=101825.msg403434#msg403434 date=1153862004]
no i'm really just returning the last cell in my column--which doesn't come close to what the avg should really be-- i'm just confused. is there any other way to do this?
[/quote]

The way you've been shown should work. Are you sure of the values in the table? Can you look at all the rows that have column TopicTitle with the value you are using and work out what value to expect? What happens if you leave the WHERE clause out of the query?