I have this table in mysql in which the results of games played are saved. In this case, it refers to baseball. As you can see in this table, I have these fields for recording each result:
visitor_id -> team that acted as visitor away -> runs made by the away team local -> races run by the local team id_local -> computer that acted as local
id_dominio id_visitante visitante local id_local id_temporada
1 1 0 10 3 1
2 2 8 5 4 1
3 6 2 15 7 1
4 11 8 2 13 1
5 3 8 2 1 1
6 2 3 7 11 1
7 7 5 0 4 1
8 1 0 6 3 1
9 13 7 1 8 1
10 4 4 10 7 1
What I need is to obtain the total number of games that one team has won against another in the clashes that have taken place between them.
For example, in the times that they have faced each other 1 vs 3, how many times has 3 won?
Until now I have managed to obtain the results given between two same teams but not the domain between those two teams
SELECT * FROM dominio WHERE (id_local = 3 AND id_visitante = 1) OR (id_local = 1 AND id_visitante = 3) AND id_temporada = 1
Here is an approach based on
SUM
usingCASE
:This query creates two columns
OneWins
andThreeWins
in each of them it will show the number of times team 1 beat team 3 and vice versa.An interesting context is that of the draw. In this type of query, assuming that both teams have beaten the other
5
times, the result would be this:I bring up this possibility of a tie because it could complicate any code if what you are interested in is knowing which team dominates the other. In that case, it would be best to create a function that checks the number of victories of each team when they face the other, that checks if they are equal to return one result
0
or another or return the team that dominates the other and the number of victories over it. .