Mysql sports league table

Question

I am creating a sports page and I have reached to a point that I can't solve myself.

My problem is that I can´t figure out how to store positions of each team separate after each matchday. Here is what I want to achieve:

link

For creating the league tables I have two tables:

clubs: id_clubs, name
results: matchid, matchday,hometeam,awayteam,homegoals,awaygoals,date,id_league

From the results table I create my league table that is setup like this:

SELECT
    name AS Team,
    Sum(P) AS P,
    Sum(W) AS W,
    Sum(D) AS D,
    Sum(L) AS L, 
    SUM(F) as F,
    SUM(A) AS A,
    SUM(GD) AS GD,
    SUM(Pts) AS Pts  
FROM ( 
    SELECT *,
        hometeamid Team,  
        1 P, 
        IF(homegoal > awaygoal,1,0) W, 
        IF(homegoal = awaygoal,1,0) D, 
        IF(homegoal < awaygoal,1,0) L, 
        homegoal F, 
        awaygoal A, 
        homegoal-awaygoal GD, 
        CASE WHEN homegoal > awaygoal THEN 3
             WHEN hometeam = awaygoal THEN 1
             ELSE 0
        END PTS 
    FROM results
    WHERE id_league = '$leagueid'
      AND homegoal IS NOT NULL
      AND awaygoal IS NOT NULL

    UNION ALL 

    SELECT *,
        awayteamid, 
        1, 
        IF(homegoal < awaygoal,1,0), 
        IF(homegoal = awaygoal,1,0), 
        IF(homegoal > awaygoal,1,0), 
        awaygoal, 
        homegoal, 
        awaygoal-homegoal GD, 
        CASE WHEN homegoal < awaygoal THEN 3
             WHEN homegoal = awaygoal THEN 1
             ELSE 0
        END 
        FROM results
        WHERE id_league = '$leagueid' 
          AND awaygoal IS NOT NULL
          AND homegoal IS NOT NULL
) AS tot 
JOIN clubs t ON tot.Team=t.id_clubs  
GROUP BY Team  
ORDER BY
    SUM(Pts) desc,
    SUM(GD) desc

Hopefully someone can give some tips or a solution.

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

If your question about retrieving the order number from your resulting table, here is a similar discussion which might be exactly what you need: MySQL - Get row number on select

To select first n matches, you can do LIMIT n after grouping by match id.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 29 '13 at 03:35

sashkello

17,306
24
81
109

My aim is to get the position of each team after every matchday. This is an example for one team: Manchester City: Matchday 1: 1st Matchday 2: 3rd Matchday 3: 3rd Matchday 4: 7th and so on. My goal is to get the positions 1,3,3,7 so I can make a graph like shown in the image in my first post – EyemansDome Apr 29 '13 at 14:45

Mysql sports league table

1 Answers1