Search code examples
mysqlsqlsumaverageunpivot

Nested grouping mysql

I have a table results with the following important columns:

  • f_team1 (VARCHAR)
  • f_team2 (VARCHAR)
  • f_player1 (VARCHAR)
  • f_player2 (VARCHAR)
  • f_total_ftg (INT)

A team can be in either f_team1 or f_team2 columns

A player can be in either f_player1 or f_player2 columns

I'm trying to find the AVG of f_total_ftg for each player, and then taking that AVG and applying it per team. So the team_average would be the sum of all the individual player averages.

My attempt:

SELECT team,
(
        SELECT AVG(player_team_average) AS players_team_average
                 FROM
                 (
                   SELECT AVG(CASE WHEN f_team1='arsenal' OR f_team2='arsenal' THEN f_total_ftg END) AS team_average
                    FROM
                    (
                        SELECT f_player1 AS player, f_team1, f_team2, f_total_ftg FROM results
                        UNION
                        SELECT f_player2 AS player, f_team1, f_team2, f_total_ftg FROM results
                    ) x GROUP BY player
                 ) x
) AS team_average FROM (
    
    (SELECT f_team1 AS team, f_total_ftg FROM results)
    UNION
    (SELECT f_team2 AS team, f_total_ftg FROM results)
    ) x GROUP BY team

Current output:

enter image description here

As you can see from my code, i've put the team as arsenal and this gives the correct overall average for arsenal across different players. How can I do this for every team? I need to sub out the string and apply the average for each team.

How can I do this?

Table looks like this:

enter image description here

Solution

  • Is this what you want?

    select team, sum(avg_ftg) sum_avg_ftg
from (
    select team, player, avg(f_total_ftg) avg_ftg
    from (
        select f_team1 team, f_player1 player, f_total_ftg from mytable
        union all select f_team2, f_player2, f_total_ftg  from mytable
    ) t
    group by team, player
) t
group by team

    This unpivots the team/players tuples to rows, then compute the average ftg per player and team, and finally sums he average per team.

    In very recent versions of MySQL, this can be more efficiently expressed with a lateral join:

    select team, sum(avg_ftg) sum_avg_ftg
from (
    select x.team, x.player, avg(t.f_total_ftg) avg_ftg
    from mytable t
    cross join lateral (
        select t.f_team1 team, t.f_player1 player
        union all select t.f_team2, t.f_player2
    ) x
    group by x.team, x.player
) t
group by team