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