CODEWITHSUNDEEP
mysql
user730952
user730952

Reputation: 61

MySQL SELECT query to create ladder in sports competition

I am trying to create a sports fixture and ladder for a sports competition. I am having trouble creating the ladder.

I would like the ladder to look something like this. Teams get 2 points for a win or bye and 1 point for a draw.

And I would like to create that ladder using a table that looks like this

This is as far as I have gotten, but it only returns the names of teams who have won a game and how many games they have won:

SELECT teamname,COUNT(*) as wins 
FROM result where pointsfor > pointsagainst and completed = "1" 
GROUP BY teamname ORDER BY wins DESC;

Upvotes: 0

Views: 448

Answers (1)

OMGKurtNilsen
OMGKurtNilsen

Reputation: 5098

Try using subqueries like this:

SELECT
    teamname,
    (SELECT COUNT(*) FROM result WHERE teamname = r.teamname AND pointsfor > pointsagainst AND completed = 1) AS wins,
    (SELECT COUNT(*) FROM result WHERE teamname = r.teamname AND pointsfor < pointsagainst AND completed = 1) AS losses,
    (SELECT COUNT(*) FROM result WHERE teamname = r.teamname AND pointsfor = pointsagainst AND completed = 1) AS draws
FROM result AS r
GROUP BY teamname
ORDER BY wins DESC

Edit: Added Group by

Upvotes: 1

Related Questions