'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 |
|---|
