SUM Two SELECT Queries

[phphit]

I have two tables (points, pointsmatch)

Table 'points' have following fields
id (int)
seriesid (int)
team (varchar)
points (int)

id seriesid team points
1 9 Chennai 4
2 9 Delhi 0
3 9 Kolkata 0
4 9 Mumbai 0
5 9 Bangalore 0
6 9 Mohali 0
7 9 Rajasthan 0
8 9 Pune 0
9 9 Hyderabad 0

Table 'pointsmatch' will list each match details alsong with points table which have following fields
id (int)
seriesid (int)
team1 (varchar)
team2 (varchar)
team1_points (int)
team2_points (int)

id team1 team2 seriesid result team1_points team2_points
1 Chennai Mumbai 9 won 2 0
2 Kolkata Delhi 9 loss 0 2
3 Mumbai Pune 9 loss 0 2
4 Rajasthan Mohali 9 won 2 0
5 Bangalore Delhi 9 won 2 0
6 Hyderabad Chennai 9 loss 0 2
7 Rajasthan Kolkata 9 won 2 0

Below is query I am executing

UPDATE points A SET A.points = (SELECT sum(team1_points) as points1 FROM pointsmatch B WHERE A.team = B.team1 AND A.seriesid = B.seriesid GROUP BY B.team1) + (SELECT sum(team2_points) as team2_points FROM pointsmatch B WHERE A.team = B.team2 AND A.seriesid = B.seriesid GROUP BY B.team2)

But only three rows are being updated and not the all (total 9 rows). Anyone here to help me.

[CyborgShadow]

UPDATE points p 
       JOIN (SELECT p.team, 
                    Sum(team1_points) AS points 
             FROM   points p 
                    JOIN pointsmatch pmt1 
                      ON p.team = pmt1.team1 
             UNION ALL 
             SELECT p.team, 
                    Sum(team2_points) 
             FROM   points p 
                    JOIN pointsmatch pmt2 
                      ON p.team = pmt2.team2) sums 
         ON sums.team = p.team 
SET    p.points = sums.points; 

[phphit]

No, its not working. Any other solution ?

[CyborgShadow]

Can you define "not working"? That query will set the value for a team in the "points" table to the sum of all points acquired by that team in the pointsmatch table.

[phphit]

It's just updating one row, that too with wrong value. The value has to be 4 while its showing 8

[phphit]

The below query results into two fields though need to be grouped to sum the points.

SELECT team1 AS team, sum(team1_points) AS points FROM pointsmatch B, points A WHERE A.team = B.team1 AND A.seriesid = B.seriesid AND A.groupid = B.groupid GROUP BY team1 UNION ALL SELECT team2 AS team, sum(team2_points) AS points FROM pointsmatch B, points A WHERE A.team = B.team2 AND A.seriesid = B.seriesid AND A.groupid = B.groupid GROUP BY team2

team points
Chennai 2
Hyderabad 0
Kolkata 0
Mumbai 0
Rajasthan 4
Bangalore 2
Chennai 2
Delhi 2
Mohali 0
Kolkata 0
Mumbai 0
Pune 2

So Now I need one update query which would also group the team and points. If anyone can help me how to group, it would be really helpful for me.

[CyborgShadow]

There's no way the query I wrote will only update a single row, and with bad values unless your team names don't match.

Do you have whitespace on either side of your team names?

Try this query:

SELECT team1, length(team1), team2, length(team2) from pointsmatch;

Then compare what you see visually with the results of the length. If the length is different, you'll need to trim/cleanup your team names.

[phphit]

I have modified your code by adding GROUP BY filter.

UPDATE points p 
       JOIN (SELECT p.team, 
                    Sum(team1_points) AS points 
             FROM   points p 
                    JOIN pointsmatch pmt1 
                      ON p.team = pmt1.team1 GROUP BY team1
             UNION ALL 
             SELECT p.team, 
                    Sum(team2_points) AS points 
             FROM   points p 
                    JOIN pointsmatch pmt2 
                      ON p.team = pmt2.team2 GROUP BY team2) as sums 
         ON sums.team = p.team 
SET    p.points = sums.points

Few of the rows are not being updated correctly. In my view its not doing sum function correctly. It's just showing which ever is the higest value. Chennai must have got 4 points while its just showing 2 points.

[phphit]

SELECT team, SUM(totalpoints) FROM
(
SELECT p.team, 
                        Sum(team1_points) AS totalpoints 
                 FROM   points p 
                        JOIN pointsmatch pmt1 
                          ON p.team = pmt1.team1 GROUP BY team1
                 UNION ALL
                 SELECT p.team, 
                        Sum(team2_points) AS totalpoints
                 FROM   points p 
                        JOIN pointsmatch pmt2 
                          ON p.team = pmt2.team2 GROUP BY team2 ) sum

GROUP BY team;

Below query results into correct table. Now, how I update the table ?

[phphit]

Ok. Problem Solved.

UPDATE points p 
       JOIN (SELECT team, SUM(totalpoints) as points FROM
(
SELECT p.team, 
                        Sum(team1_points) AS totalpoints 
                 FROM   points p 
                        JOIN pointsmatch pmt1 
                          ON p.team = pmt1.team1 GROUP BY team1
                 UNION ALL
                 SELECT p.team, 
                        Sum(team2_points) AS totalpoints
                 FROM   points p 
                        JOIN pointsmatch pmt2 
                          ON p.team = pmt2.team2 GROUP BY team2 ) sum

GROUP BY team
) as sums 
         ON sums.team = p.team 
SET    p.points = sums.points