0

I have two tables Teams and Players. A Team can have an unlimited number of Players and a Player can only belong to one team.

Each Player on a team has a wage.

I would like to get the average wage of the top 16 players at each team (or the less if there are less than 16 players).

Here is an example schema:

CREATE TABLE `teams` (
  `id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aw16 DECIMAL(10,2) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `players` (
  `id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `team_id` INT(10) UNSIGNED NOT NULL,
  `wage` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I can get the top 16 average for one team at a time quite easily like so:

SELECT teams.name, AVG(players.wage)
FROM teams
INNER JOIN (
SELECT wage, team_id
FROM players
WHERE team_id = 1
ORDER BY wage DESC
LIMIT 16
) players
ON team_id = teams.id
GROUP BY teams.id

But I can't figure out how to do it for all Teams in one query so I can wind up with the "Team ID" and the "Top 16 Average" in two columns.

What's more I actually want to do an update and fill the aw16 field in the Teams table with the calculation for each team, but that seems even more problematic.

John Mellor
  • 2,351
  • 8
  • 45
  • 79
  • Basically you need to get the top 16 wages for each team using one of the methods in the duplicates, and then use that query as the derived table in your existing query. – Nick Oct 24 '19 at 04:31

1 Answers1

1

try this out

SELECT teams.name, AVG(players.wage)
FROM teams
INNER JOIN (
select wage, team_id
from players
where (
   select count(*) from players as p
   where p.team_id = players.team_id and p.wage >= players.wage
) <= 16
) players
ON team_id = teams.id
GROUP BY teams.id
1encore
  • 394
  • 4
  • 15