agustincola agustincola - 25 days ago 7
MySQL Question

Sum the result of a SELECT that returns several rows

i am trying to sum the result of a select that return several rows:
i have four tables (user, team, pilot, result):

user (id_user)
team (id_user, id_team)
pilot (id_pilot, id_team)
result (id_pilot, id_gp, points)


When i do that:

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


i get:

id_team TOTAL
-------------
1 10
2 15
3 5
4 6
5 7


what i want is, if id_user has id_team 1 and 2 has 25 points.

i try that:

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


but i only have the points of one team.

thanx very much in advance how can help me.

Answer

Are you looking to get the SUM of points for each user, instead of each team?

If so, your query might be something like this:

SELECT u.id_user, SUM( r.points ) AS TOTAL
FROM pilot p
LEFT JOIN result r ON p.id_pilot = r.id_pilot
INNER JOIN team t ON p.id_team = t.id_team
INNER JOIN user u ON t.id_user = u.id_user
WHERE r.id_grand_prix =  '1'
GROUP BY u.id_user

If not, could you clarify what you need to display?