Jump to content


Photo

Adding Up Numbers in Mysql Table


  • Please log in to reply
12 replies to this topic

#1 gladiator83x

gladiator83x
  • Members
  • PipPipPip
  • Advanced Member
  • 45 posts

Posted 25 July 2006 - 05:18 PM

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.


#2 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 25 July 2006 - 05:22 PM

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

#3 leeming

leeming
  • Members
  • PipPipPip
  • Advanced Member
  • 93 posts

Posted 25 July 2006 - 05:26 PM

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

kinda like that
Im not perfect ;) lol

#4 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 25 July 2006 - 05:32 PM

there ya go  :)

#5 gladiator83x

gladiator83x
  • Members
  • PipPipPip
  • Advanced Member
  • 45 posts

Posted 25 July 2006 - 05:34 PM

Thanks a ton!!!      :)

#6 gladiator83x

gladiator83x
  • Members
  • PipPipPip
  • Advanced Member
  • 45 posts

Posted 25 July 2006 - 06:16 PM

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.

#7 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 25 July 2006 - 06:20 PM

Use mysql_fetch_assoc

mysql_fetch_result doesnt exist.

#8 gladiator83x

gladiator83x
  • Members
  • PipPipPip
  • Advanced Member
  • 45 posts

Posted 25 July 2006 - 06:33 PM

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";
}




#9 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 25 July 2006 - 07:52 PM

There will only be one value in the array - the average of all the rows for that column.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#10 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 25 July 2006 - 08:22 PM

i think the average equals to the last record so it appears that you're getting the last one.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#11 gladiator83x

gladiator83x
  • Members
  • PipPipPip
  • Advanced Member
  • 45 posts

Posted 25 July 2006 - 09:13 PM

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?

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 July 2006 - 11:20 PM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 25 July 2006 - 11:49 PM

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?


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?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users