Mysql Query SUM function

[iStriide]

This bit of code will not work and I'm hoping that you guys can help me to make it work.

 

<?php
mysql_connect("connect.php");

$query = "SELECT  SUM(Games) FROM stats"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "Total Games: $row['SUM(Games)']";
}
?>

[Psycho]

I'll give you a hint. The MySQL SUM() function is listed in the manual under

11.15.1. GROUP BY (Aggregate) Functions

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

 

Check this tutorial on using SUM()

http://www.tizag.com/mysqlTutorial/mysqlsum.php

[DavidAM]

"will not work" is not very meaningful. What happens? Do you get an error message? Does it print "Total Games 0"? or what? Since you are using fetch_array, you can try $row[0] to get the value, or provide an alias for the column:

 

$query = "SELECT  SUM(Games) AS TotalGames FROM stats"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
$row = mysql_fetch_array($result);
echo "Total Games: $row['TotalGames']";

 

by the way, you don't have to use a loop since there will be only one row returned from that query.

 

@mjdamato:

You can use SUM without a GROUP BY. Since there are no other columns in the SELECT, it will return the SUM across all rows that satisfy the WHERE clause (or in this case, the entire table).

[Psycho]

You can use SUM without a GROUP BY. Since there are no other columns in the SELECT, it will return the SUM across all rows that satisfy the WHERE clause (or in this case, the entire table).

 

Yeah, I knew that, don't know what I was thinking. I see that the problem is he is referring to the result field incorrectly. I don't know if there is a default field name given with a group by aggregate function. So, you are right he could use the 0 index or give the field an alias int he select query, which is whay I usually do.

mysql_connect("connect.php");

$query = "SELECT  SUM(Games) as gamesSum FROM stats"; 
$result = mysql_query($query) or die(mysql_error());

// Print out result
$row = mysql_fetch_array($result))
echo "Total Games: $row['gamesSum']";

[DavidAM]

Yeah, I always alias calculated columns  -- then I don't have to try to remember how mySql assigns the column name (which I think would be "SUM (games)" (with a space in there)), and I always use fetch_assoc (I don't see any sense in having the extra array elements, and it just seems dangerous referring to the columns by number).

[Psycho]

I always use fetch_assoc (I don't see any sense in having the extra array elements, and it just seems dangerous referring to the columns by number).

Finally, someone that thinks like me. I've never understood any reason to use mysql_fetch_array() when mysql_fetch_assoc() has all the data you need, and nothing else, with all of it nicely associated with keys that *should* have intuitive names.