Fantasy:
========
SELECT name, team, avg(average) AS team_avg
FROM players
GROUP BY team;

   name    |   team   | team_avg
-----------+----------+----------
 Jones     | Bastards |    0.255
 Harvey    | Bastards |    0.255
 Simon     | Bastards |    0.255
 Kerouac   | Liars    |    0.257
 Burroughs | Liars    |    0.257
 Ginsberg  | Liars    |    0.257
 Albini    | Cheaters |    0.275
 Vig       | Cheaters |    0.275
 Tiers     | Cheaters |    0.275
(9 rows)

Reality:
========
% cat select.sql
SELECT name, average, team
FROM players;

SELECT P.name, P.team, T.team_avg
FROM players AS P join
(
        SELECT team, round(avg(average), 3) AS team_avg
        FROM players
        GROUP BY team
) AS T
ON P.team = T.team;

% psql TEMP < select.sql
   name    | average |   team
-----------+---------+----------
 Jones     |   0.215 | Bastards
 Harvey    |   0.250 | Bastards
 Simon     |   0.300 | Bastards
 Kerouac   |   0.245 | Liars
 Burroughs |   0.320 | Liars
 Ginsberg  |   0.205 | Liars
 Albini    |   0.285 | Cheaters
 Vig       |   0.290 | Cheaters
 Tiers     |   0.250 | Cheaters
(9 rows)

   name    |   team   | team_avg
-----------+----------+----------
 Jones     | Bastards |    0.255
 Harvey    | Bastards |    0.255
 Simon     | Bastards |    0.255
 Kerouac   | Liars    |    0.257
 Burroughs | Liars    |    0.257
 Ginsberg  | Liars    |    0.257
 Albini    | Cheaters |    0.275
 Vig       | Cheaters |    0.275
 Tiers     | Cheaters |    0.275
(9 rows)