Query that countes pairs with same values depending on third column

Question

I have three columns: Team_Code, ID, times_together. I'm trying to count how many times ID's have the same "Team_Code" and add times_together to it. In other words- I'm trying to write all the pairs of one column, check how many times they have the same value in other raw, and add third raw to it.

The simple way to ask this question is picture so:

Values can appear twice (for example 1110 with 8888 and then 8888 with 1110).

Answer 1

You could self join the table on team_code and sum the times_together:

SELECT t1.id, t2.id, SUM(t1.times_together)
FROM   mytable t1
JOIN   mytable t2 ON t1.team_code = t2.team_code AND t1.id != t2.id

If you want to make sure each pair only appears once, you could add a condition to always take the lower id on the left:

SELECT t1.id, t2.id, SUM(t1.times_together)
FROM   mytable t1
JOIN   mytable t2 ON t1.team_code = t2.team_code AND t1.id < t2.id