Combine Results in Same Column to get Average Lets say I have a table that looks like the following:




Average Score
Team
Month




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




I want to get the average for certain combinations of teams. How would I get the average of New York Team + San Fran team in May, and the average for the Chi team + Texas team?
Basically I want New York + San Fran to = Tier 1 (average)
and Chi + Texas to = Tier 2 (average)
Thanks!
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