# 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
("Average Score" int, "Team" varchar(13), "Month" varchar(4))
;

INSERT INTO sportstable
("Average Score", "Team", "Month")
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 "Month",
MAX(CASE WHEN "Team" = 'New York Team' THEN "Average Score"
ELSE 0
END) AS "New York Team",
MAX(CASE WHEN "Team" = 'San Fran Team' THEN "Average Score"
ELSE 0
END) AS "San Fran Team",
MAX(CASE WHEN "Team" = 'Chi Team' THEN "Average Score"
ELSE 0
END) AS "Chi Team",
MAX(CASE WHEN "Team" = 'Texas Team' THEN "Average Score"
ELSE 0
END) AS "Texas Team"
FROM
sportstable
GROUP BY "Month")
SELECT
"Month"
,("New York Team" + "San Fran Team" ) AS Tier1
,("Chi Team" + "Texas Team") AS Tier2
FROM
average_month
``````
```Month | tier1 | tier2
:---- | ----: | ----:
May   |     5 |     5
June  |     5 |     5
```

db<>fiddle here