Looping to SUM values

[Donovan]

I have several teams in a table defined by team_id.  Each team may have several records in this table with a resource point defined by r_points equaling  anywhere from 5 to -5.

 

$sql = $db->sql_query("SELECT * FROM " . $prefix . "_tc_ladderteams tclt
JOIN " . $prefix . "_tc_teams tct ON (tclt.team_id = tct.team_id)	
LEFT JOIN " . $prefix . "_eto_territories et ON (tct.tid = et.tid)	
LEFT JOIN " . $prefix . "_eto_divisions ed ON (tct.div_id = ed.div_id)
JOIN " . $prefix . "_tc_ladders tcl ON (tclt.ladder_id = tcl.sid)
WHERE enabled = 1 AND (ed.div_commander = '$nukeusername' || ed.div_xo ='$nukeusername')
ORDER BY 'name'");

 

I then need to SUM the resource points for each team and display

 

while ( $row = $db->sql_fetchrow($sql) ) {
		$team_id = $row['team_id'];
		$team_name= $row['name'];
		$t_name= $row['t_name'];
$result4 = $db->sql_query ("SELECT SUM(lkup.r_points) as resourcepoints 
FROM ".$prefix."_eto_rpoints_lkup lkup 
JOIN ".$prefix."_tc_teams tct 
WHERE lkup.team_id = tct.team_id 
AND (lkup.rp_id = '4' OR lkup.rp_id = '5')");
$info4 = $db->sql_fetchrow($result4);
$totalpoints += $info4['resourcepoints'];

    echo"<tr>"	
. "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$team_name</font></td>"	
. "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$totalpoints</font></td>"
. "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$t_name</font></td>"
. "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">[ <a href=\"modules.php?name=Campaign&file=Reenforce&team_id=$team_id\">" . _CLICK . "</a>]</td>"
. "</tr>";
}
echo "</table>"

 

 

I know this query works as I've tested it several time.

 

My problem is displaying all teams in the table listed by teamname, totalpoints, then t_name.

 

What is happening is the first team may SUM to 5.  The next team SUMS to 10 the next to 15 then 20, then 25 and so on.

 

What I need is some kind of subquery for each teams $totalpoints, but am unsure how to do it.

 

So to clarify for each team name that is different I need to start the total points over again?

 

This is a bug I've had for some time and I kept pushing it to the back burner.

[tippy_102]

Do you want them listed alphabetically by Team name?  If so, you could do a "SELECT DISTINCT ... ORDER BY team_name", then, while looping through those results do your summing code above....or maybe I don't understand the question.