MySQL SUM (what am I doing wrong?)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Jr
Forum Commoner
Posts: 99
Joined: Mon Mar 07, 2005 3:25 pm

MySQL SUM (what am I doing wrong?)

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at what mysql_fetch_array() returns.
Jr
Forum Commoner
Posts: 99
Joined: Mon Mar 07, 2005 3:25 pm

Post 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?
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post 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";
  }
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:look at what mysql_fetch_array() returns.
Array ( [sum(`Rating`)] => 5 )?

Is this "[sum(`Rating`)]" the key?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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 )
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It might be a good idea to not further hijack Jr's thread.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:It might be a good idea to not further hijack Jr's thread.
I'm not.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply