Donovan Posted March 17, 2007 Share Posted March 17, 2007 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. Quote Link to comment Share on other sites More sharing options...
tippy_102 Posted March 21, 2007 Share Posted March 21, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.