title: Sum the result of a SELECT that returns several rows
body: <p>i am trying to sum the result of a select that return several rows:
  i have four tables (user, team, pilot, result):</p>
  
  <pre><code>user (id_user)
  team (id_user, id_team)
  pilot (id_pilot, id_team)
  result (id_pilot, id_gp, points)
  </code></pre>
  
  <p>When i do that:</p>
  
  <pre><code>SELECT p.id_team, SUM( r.points ) AS TOTAL
  FROM pilot p
  LEFT JOIN result r ON p.id_pilot = r.id_pilot
  WHERE r.id_grand_prix =  '1'
  GROUP BY p.id_team
  </code></pre>
  
  <p>i get:</p>
  
  <pre><code>id_team TOTAL
  -------------
  1 10
  2 15
  3 5
  4 6
  5 7
  </code></pre>
  
  <p>what i want is, if id_user has id_team 1 and 2 has 25 points.</p>
  
  <p>i try that:</p>
  
  <pre><code>UPDATE user AS u 
  JOIN has_team ON u.id_user = has_team.id_user 
  JOIN (
      SELECT p.id_team, SUM( r.position ) AS TOTAL
      FROM pilot p
      LEFT JOIN result r ON p.id_pilot = r.id_pilot
      WHERE r.id_grand_prix =  '1'
      GROUP BY p.id_team
      ) AS grp 
    ON grp.id_team = has_team.id_team
  SET u.points = grp.TOTAL + u.points
  </code></pre>
  
  <p>but i only have the points of one team.</p>
  
  <p>thanx very much in advance how can help me.</p>