ALUFTW ALUFTW - 7 months ago 18
SQL Question

Query that countes pairs with same values depending on third column

I have 3 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:
enter image description here

EDIT: I don't care if the values will appear twice (for example
1110 with 8888
and then
8888 with 1110).

Thanks All!

Answer

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
Comments