CODEWITHSUNDEEP
mysqlsqldatabase
Michael Abraham
Michael Abraham

Reputation: 57

SQL Query to Count who has the highest number of match victories

I'm creating a simple database which will allow me to track snooker results, producing head to head results between players. Currently I have 3 tables: (Player, Fixture, Result)

PlayerID    PlayerName
1           Michael Abraham
2           Ben Mullen
3           Mark Crozier

FixtureID   Date                 TableNo    Group
1           07/12/2015 19:00:00  12         0
2           08/12/2015 12:00:00  9          0

ResultID    FixtureID   PlayerID    FramesWon
1           1           1           3
2           1           3           1
3           2           1           2
4           2           3           5

As you can see in the Result table, Player1 has played Player3 two times, with Player1 winning the first match 3-1, and Player3 winning the second match 5-2. I would like a query which returns the total number of matches won between the two players. In this case the expected output should be:

PlayerID    MatchesWon
1           1
3           1

Any help would be appreciated - I'm not even sure if this can be achieved via a query

Upvotes: 1

Views: 1313

Answers (3)

Wilianto Indrawan
Wilianto Indrawan

Reputation: 2404

This is the alternative you can try.

SELECT r.PlayerID, COUNT(r.PlayerID)
FROM (
    SELECT FixtureID, MAX(FramesWon) AS FramesWon
    FROM `result`
    GROUP BY FixtureID    
) win
INNER JOIN result r ON win.FixtureID = r.FixtureID AND win.FramesWon = r.FramesWon
GROUP By r.PlayerID

Upvotes: 0

Turophile
Turophile

Reputation: 3405

Perhaps this would work for you:

select playerid, count(*) as matcheswon
from result as r1
where frameswon = 
 (
   select max(frameswon)
   from result as r2
   where r2.fixtureid = r1.fixtureid
   )
group by playerid

In a fiddle here: http://sqlfiddle.com/#!9/60821/2

Upvotes: 0

RJD
RJD

Reputation: 67

I agree using windowing function would be best way to go if available (SQL Server for example) Might be possible with a straight SQL method this way (given that the one having most wins in a "fixture" is the match winner)

    SELECT PlayerId, FixtureID, Count(*) As MatchesWon
    FROM Result r 
    WHERE r.Frameswon = (SELECT MAX(frameswon) FROM Result r2
                  WHERE 
                  r.FixtureId = r2.FixtureId)
    GROUP BY PlayerID,FixtureId

OR if can leave out the fixtureId, and filter for just the 2 players something like this one as well. with data given above should bring the sample results.

    SELECT PlayerId, MatchesWon 
    FROM
    (
    SELECT FixtureID,PlayerId, Count(*) As MatchesWon
    FROM Result r 
    WHERE r.Frameswon = (SELECT max(frameswon) FROM Result r2
                  WHERE 
                  r.FixtureId = r2.FixtureId)

   GROUP BY FixtureId,PlayerID
   ) s
   WHERE 
   PlayerID IN (1,3)

Upvotes: 1

Related Questions