CODEWITHSUNDEEP
mysqlsqlcount
zeid10
zeid10

Reputation: 541

how to calculate head to head stats using mysql

I am trying to calculate head to head stats when two teams play against each other. Team1 can play against Team2 multiple times through out the season. Everytime there is a head to head match(game) I record the results i n the head to head table. Below is my table schema that holds the data:

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'match up id',
`team1_id` int(11) DEFAULT NULL COMMENT 'id of team1',
`team2_id` int(11) DEFAULT NULL COMMENT 'id of team2',
`winner` int(11) DEFAULT NULL COMMENT 'winner(team ID) of this head to head',
`dateplayed` date DEFAULT NULL COMMENT 'date played',
`tournament_id` int(11) DEFAULT NULL COMMENT 'tournament name',

My goal is to be able to calculate how many wins did team1(team1_id) had against team2(team2_id). The query I am using now is:

SELECT  count(winner)as wins, te.id FROM head_to_head hh
    LEFT JOIN teams te ON
    hh.winner = te.id
    WHERE hh.team1_id ='225' AND
    hh.team2_id ='4'
    group by te.id
    order by count(winner)

If I use the query above i only get the "wins" for the hh.team2_id('4'). If they played against each 5 time during the season I want to be able to calculate team1 won 'x' amount of times and team2 won 'x' amount of times.So the end result should look like this: --Assuming they played 5 times against each other during the season.

[team_id] [Wins]
   225      3
    4       2

Any help is great appreciated. thank you.

Upvotes: 1

Views: 346

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

select 
  t.id team_id,
  count(h.winner) wins
from teams t
  left outer join
head_to_head h
  on t.id = h.winner
  and h.team1_id ='225'
  and h.team2_id ='4'
group by t.id;

Upvotes: 1

Related Questions