Query DB - GROUP BY - total question

[mishrathium]

Lets start off by saying I'm a coding noob.

This is what I want to occur. Two fields in my DB looks like this.

estate_owner -
estate_level -

estate_owner can have the same name. Like the following.

estate_owner - mishrathium
estate_level - 5

estate_owner - mishrathium
estate_level - 10

estate_owner - mishrathium
estate_level - 3

I want to query that DB and get a TOTAL of estate_level for each estate_owner with the same name.

example.

estate owner - Mishrathium
Total level - 18

I can do this for ONE person at a time, but not for the entire DB.

Sample code:

	$query = "SELECT estate_level,estate_owner FROM user_estate GROUP BY (estate_owner)";  

  	$result = mysql_query($query); $numrows = mysql_num_rows($result);
echo "<table> ";
echo '<font color="#808080">Top 10 Estates </font><hr>';
	if (mysql_num_rows($result) > 0)
	{
	// $defend = 0 ; $defender_power = 0 ;
	while($row = mysql_fetch_array($result))
	{
	$defend = $row[estate_level] ;
	$defender_power = $defender_power + $defend ;
	$estate_owner = $row[estate_owner]; 
		echo "<tr> <td> $estate_owner </td> <td> defense </td> <td> $defender_power </td> </tr>";
	} 

}
echo "</table>";

I have tried many different combinations, including ORDER BY but the total amount is wrong.

One last thing. I only want to show the 10 highest totals.
ORDER BY estate_level DESC LIMIT 10

Any help is much appreciated.

[toplay]

Something like this:

$query = "SELECT SUM(estate_level) AS estate_total, estate_owner 
            FROM user_estate 
            GROUP BY estate_owner
            ORDER BY estate_total DESC LIMIT 10";  

$result = mysql_query($query); 
if (!$result) {
   // Handle error
   echo 'SQL: ', $query, ' Error: ', mysql_error();
   exit;
}

echo "<table> ";
echo '<font color="#808080">Top 10 Estates </font><hr>';

while($row = mysql_fetch_assoc($result)) {
    echo "<tr><td> {$row['estate_owner']} </td><td> defense </td> <td> {$row['estate_total']} </td> </tr>";
} 

echo "</table>";

[mishrathium]

That did the trick. I've spent countless hours searching the web for that.

many many thanks.

works like a charm.

[toplay]

Great and you're welcome.

FYI: SQL SUM() manual page:
http://dev.mysql.com/doc/mysql/en/group-by-functions.html#id3154963

Happy coding.

🙂