Combine Results in Same Column to get Average
You can use a with clause and get so the average from every team per month and then simply add the data as you see fit


CREATE TABLE sportstable
    (&quot;Average Score&quot; int, &quot;Team&quot; varchar(13), &quot;Month&quot; varchar(4))
;
    
INSERT INTO sportstable
    (&quot;Average Score&quot;, &quot;Team&quot;, &quot;Month&quot;)
VALUES
    (2, 'New York Team', 'May'),
    (3, 'San Fran Team', 'May'),
    (2, 'Chi Team', 'May'),
    (3, 'Texas Team', 'May'),
    (2, 'New York Team', 'June'),
    (3, 'San Fran Team', 'June'),
    (2, 'Chi Team', 'June'),
    (3, 'Texas Team', 'June')
;


✓

8 rows affected




WITH average_month AS (SELECT &quot;Month&quot;,
MAX(CASE WHEN &quot;Team&quot; = 'New York Team' THEN &quot;Average Score&quot;
            ELSE 0
       END) AS &quot;New York Team&quot;,
       MAX(CASE WHEN &quot;Team&quot; = 'San Fran Team' THEN &quot;Average Score&quot;
            ELSE 0
       END) AS &quot;San Fran Team&quot;,
       MAX(CASE WHEN &quot;Team&quot; = 'Chi Team' THEN &quot;Average Score&quot;
            ELSE 0
       END) AS &quot;Chi Team&quot;,
       MAX(CASE WHEN &quot;Team&quot; = 'Texas Team' THEN &quot;Average Score&quot;
            ELSE 0
       END) AS &quot;Texas Team&quot;
FROM 
   sportstable
GROUP BY &quot;Month&quot;)
SELECT 
    &quot;Month&quot;
    ,(&quot;New York Team&quot; + &quot;San Fran Team&quot; ) AS Tier1
    ,(&quot;Chi Team&quot; + &quot;Texas Team&quot;) AS Tier2
FROM
    average_month


Month | tier1 | tier2
:---- | ----: | ----:
May   |     5 |     5
June  |     5 |     5


db&lt;&gt;fiddle here