How to select SUM from multiple GROUP BY strings?

Question

I have a table of relations between players with matches and points that they scored in each. I'm trying to make SELECT, in which I get an excellent list of players with their total points, as well as the total number of points scored by all the teams on which they were. Since everything is in terms of single players, I don’t know how to get out of the GROUP BY area for just one column. For the examples below, I’ll just say that each team has only two players. In a real database, each command has five, if that matters. Thanks guys.

The table "corresponds":

match_id | winning_team | 56427859 | 0 | 56427860 | 1 | 56427861 | 1 | 56427862 | 0 | 56427863 | 1 | etc... 

Table "match_players":

 match_id | team | player_id | points | 56427859 | 0 | 10 | 3 | 56427859 | 0 | 33 | 1 | 56427859 | 1 | 26 | 0 | 56427859 | 1 | 39 | 2 | 56427860 | 0 | 23 | 1 | 56427860 | 0 | 33 | 3 | 56427860 | 1 | 18 | 1 | 56427860 | 1 | 10 | 4 | etc... 

Desired Result:

 player_id | match_count | total_points | team_total_points | <- This should be the total of all 10 | 2 | 7 | 9 | points scored by 18 | 1 | 1 | 5 | the player and 23 | 1 | 1 | 4 | his teammates 26 | 1 | 0 | 2 | in all matches. 33 | 2 | 4 | 8 | 39 | 1 | 2 | 2 | 

Request:

 SELECT p.player_id, COUNT(*) AS match_count, SUM(CASE WHEN mp.team = m.winning_team THEN 1 ELSE 0 END) AS win_count, SUM(points) AS total_points, [________________________________________] AS team_total_points FROM matches m INNER JOIN match_players mp ON m.match_id = mp.match_id INNER JOIN players p ON mp.player_id = p.player_id GROUP BY player_id ORDER BY player_id 

Edit:

The “team” column simply identifies red or blue, home or away, etc. Players can be in different teams in different matches. And players can change teams between matches, for example, on a dodgeball recess.

Answer 1

The following query will calculate the total points for all players in the same team as each player.

 SELECT p1.player_id, SUM(p2.total_points) AS team_total_points FROM match_players AS p1 JOIN (SELECT match_id, team, SUM(points) as total_points FROM match_players GROUP BY match_id, team) AS p2 ON p1.match_id = p2.match_id AND p1.team = p2.team GROUP BY p1.player_id 

You can then join this original query to add team totals.

 SELECT p.player_id, COUNT(*) AS match_count, SUM(CASE WHEN mp.team = m.winning_team THEN 1 ELSE 0 END) AS win_count, SUM(points) AS total_points, mp2.team_total_points FROM matches m INNER JOIN match_players mp ON m.match_id = mp.match_id INNER JOIN players p ON mp.player_id = p.player_id INNER JOIN (SELECT p1.player_id, SUM(p2.total_points) AS team_total_points FROM match_players AS p1 JOIN (SELECT match_id, team, SUM(points) as total_points FROM match_players GROUP BY match_id, team) AS p2 ON p1.match_id = p2.match_id AND p1.team = p2.team GROUP BY p1.player_id) AS mp2 ON mp2.player_id = p.player_id GROUP BY player_id ORDER BY player_id 

Demo

Answer 2

Try the following:

 SELECT player_id, COUNT(*) AS match_count, SUM(points) AS total_points, team_tot_points FROM match_players JOIN (SELECT SUM(points) AS team_tot_points, team FROM match_players GROUP BY team) AS team_points ON match_players.team = team_points.team GROUP BY player_id