Page 1 of 1

MySQL SUM (what am I doing wrong?)

Posted: Sat Jun 10, 2006 5:10 pm
by Jr
I dont know what I'm doing wrong here. I've found this on about a million forums, etc. saying just put the sum in the SQL query and it returns the sum of a column. Can someone help me out here?

$total_paid = mysql_query( "SELECT sum(amount) FROM table" );

if i print that value it gives me some "resource id #7" (dont know) and if I put it into a mysql_fetch_array(); and use the php array_sum(); it gives me the number 1000 which isn't correct at all either. I dont know what I'm doing wrong.

Posted: Sat Jun 10, 2006 6:14 pm
by feyd
look at what mysql_fetch_array() returns.

Posted: Sat Jun 10, 2006 6:41 pm
by Jr
hmm... what exactly do you mean? it returns an associative array or a numerical array. I'm not quite sure how that helps though. Can you explain a little more?

Posted: Sat Jun 10, 2006 7:49 pm
by derchris
Try this.
This should work.

Code: Select all

$sql = 'SELECT SUM(amount) AS total FROM `table`';
$result = mysql_query($sql) OR die(mysql_error());
  if(mysql_num_rows($result)) {
  	while($row = mysql_fetch_assoc($result)) {
  		$total_paid = $row['total'];
  		echo $total_paid;
  }
	  } else {
	  	echo "No Data";
  }

Posted: Wed Jun 28, 2006 3:09 pm
by Benjamin
feyd wrote:look at what mysql_fetch_array() returns.
Array ( [sum(`Rating`)] => 5 )?

Is this "[sum(`Rating`)]" the key?

Posted: Wed Jun 28, 2006 3:12 pm
by Benjamin
Also, if I am using this to calculate an average, how do I know how many rows were returned? I'm just trying to find a better method than looping through all the records and counting them.

Posted: Wed Jun 28, 2006 3:14 pm
by RobertGonzalez
No, The OP ran the query and got the query resource id. He didn't pipe it through fetch_array to get the actual data. derchris's example should give the OP what he is looking for.

Posted: Wed Jun 28, 2006 3:16 pm
by Benjamin
I'm saying that this..

Code: Select all

$Query = "select sum(`Rating`) from `table`";
            $Execute = mysql_query($Query, $Connect);
            $Count = mysql_fetch_assoc($Execute);
            print_r($Count);
Outputted:
Array ( [sum(`Rating`)] => 5 )

Posted: Wed Jun 28, 2006 3:20 pm
by Benjamin
Ok so I tested it, and

Code: Select all

echo $Count['sum(`Rating`)'];
outputs the sum, which is cool because I have never seen a key like that before. But now is there a way for me to find out how many rows it added together?

Posted: Wed Jun 28, 2006 3:24 pm
by feyd
It might be a good idea to not further hijack Jr's thread.

Posted: Wed Jun 28, 2006 3:27 pm
by Benjamin
feyd wrote:It might be a good idea to not further hijack Jr's thread.
I'm not.

Posted: Wed Jun 28, 2006 3:38 pm
by RobertGonzalez
astions wrote:Ok so I tested it, and

Code: Select all

echo $Count['sum(`Rating`)'];
outputs the sum, which is cool because I have never seen a key like that before. But now is there a way for me to find out how many rows it added together?
Not using SUM. You might be able to do a row_count code side, or COUNT() DB side, but trying to get the row count AND sum from the same query I don't think is going to happen.