'Using aggregate on another aggregate function - MAX() on an aggregate

I've a tournament bracket consisting of 2 Groups (Group A and Group B). I already have a query where I retreive some information such as the average rating, amount of teams etc.

To the problem: I don't want to allow 2 teams of the same group having the same color. I want a column representing the MAX() of ColorInterference without having to make a subselect.

Is this possible or am I forced to make a SELECT MAX(ColorInterference) over the query result?

Group Team Color Rating TeamsCount AverageRating AverageRatingGroup ColorInterference
A Helos Green 1452 8 1518 1544 0
A Pelicans Purple 1687 8 1518 1544 0
A Epic Square Dance Red 1498 8 1518 1544 0
A Narnia Ninjas Yellow 1542 8 1518 1544 0
B O.T. Blue 1502 8 1518 1492 0
B Helos Green 1452 8 1518 1492 1
B Treasure Goggles Green 1485 8 1518 1492 1
B Red Off Yellow 1530 8 1518 1492 0
DECLARE @Bracket_Groups Table ([Group] nvarchar(10), Team nvarchar(50), Color nvarchar(50), Rating int)
INSERT INTO @Bracket_Groups(Team, [Group], Color, Rating)
SELECT        'Narnia Ninjas', 'A', 'Yellow' , 1542
UNION SELECT  'Helos', 'A', 'Green', 1452
UNION SELECT  'Pelicans', 'A', 'Purple', 1687
UNION SELECT  'Epic Square Dance', 'A', 'Red', 1498
UNION SELECT  'O.T.', 'B', 'Blue', 1502
UNION SELECT  'Red Off', 'B', 'Yellow', 1530
UNION SELECT  'Helos', 'B', 'Green', 1452
UNION SELECT  'Treasure Goggles', 'B', 'Green', 1485

SELECT 
    [Group]
  , Team
  , Color
  , Rating
  , COUNT(*)  OVER()                                          as TeamsCount
  , AVG(Rating) OVER ()                                       as AverageRating
  , AVG(Rating) OVER (Partition By [Group])                   as Group_AverageRating
  , SIGN(COUNT(Color) OVER (partition By [Group], Color) - 1) as ColorInterference
FROM @Bracket_Groups
Order by [Group]


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source