need help on a soccer query
Posted on 2004-11-10
I created a program where clubs can see standings for their teams. with this query below, i can generate standings. this will display the one who got more points on top followed by goals in favor.
In this league there are 2 rules that i need to apply to this query. if points are the same , then it should go by head to head competition , if both are the same (points and head to head) then it isdetermined by goal differential.
I will assume that goal diferential can be created by ( SUM(fallgameresults.goalsinfavor) - SUM(fallgameresults.goalsagainst) ) how can i set this up in this query? an also how can i figure out the head to head competition in this query (this means that if team "A" played against team "B" and team "A" won the game then if it happens that they have same points in total then team "A" will be in first place)
SELECT fallgameresults.idofteam, SUM(fallgameresults.goalsinfavor) AS goals, SUM(fallgameresults.goalsagainst) AS against, SUM(fallgameresults.points) AS points, SUM(fallgameresults.win) AS win, SUM(fallgameresults.tie) AS tie, SUM(fallgameresults.loss) AS loss, SUM(fallgameresults.gameplayed) AS played, databaseteams.id , databaseteams.TeamName,databaseteams.div
FROM fallgameresults INNER JOIN databaseteams ON fallgameresults.idofteam = databaseteams.id GROUP BY fallgameresults.idofteam, databaseteams.id , databaseteams.TeamName,databaseteams.div having databaseteams.div ='varage'
ORDER BY points desc,goals desc