0

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!

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.