Adding up records in MySQL

Hello,

Let's say I have a table with the fields ID, NAME and RANK. Let's say we have:

ID | NAME | RANK
------------------
1 | John | 8
2 | Jack | 7
3 | Jack | 5
4 | John | 6


I want to add up all the values of RANK for Jack only, how would this be done?

Thank You,
Jason

 

SELECT SUM(rank) AS rank_sum FROM tableName WHERE name='Jack';

should work i think..

 

SELECT SUM(rank) FROM table WHERE name='Jack'
--the field 'rank' should be an INTEGER.

Code (SQL):

 

Hmm. This does not seem to be working all I get when I echo it is "Resource id #9" Does anybody know whats going on?

 

Well how do i reference them properly?

 

with what php command did you execute the query?

 

Here is what I have. then I go echo $sum; and it displays: Resource id #9

$sum = mysql_query("SELECT SUM(rank) FROM rank WHERE PID='$PID'") or die(mysql_error());

Code (markup):

 

you're missing a couple of statements

example at http://nl3.php.net/manual/en/function.mysql-query.php

 

All that does is run the query, you need to process the result:

$sum = mysql_query("SELECT SUM(rank) FROM rank WHERE PID='$PID'") or die(mysql_error());
$sumrank = 0;
while($sumrow = mysql_fetch_object($sum)) {
  $sumrank = $sumrow->rank;
}

PHP:

 

I am still having trouble, what variable can I echo with? Also does it matter than the field and the table are both called rank?

 

It shouldn't, but try this code:

$sum = mysql_query("SELECT SUM(rank) as Total FROM rank WHERE PID='$PID'") or die(mysql_error());
$sumrank = 0;
while($sumrow = mysql_fetch_object($sum)) {
  $sumrank = $sumrow->total;
}
echo "Rank: $sumrank";

PHP: