Complicated football league Dynamic Ordering in MySQL?

Question

I have a table 'games' for a football league as follows:

date    home_team_id    away_team_id    home_score      away_score
 -          1                 2              6             21
 -          3                 1              7             19

I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then points for if poss)?

--

I have this query which works fine when I have a $team_id but of cause then I can only do 1 team at a time, and that doesn't allow for ordering at query level

((SELECT COUNT(*) FROM `games` WHERE ((`home_score` > `away_score`) AND `home_team_id` = '.$team_id.')) + 
(SELECT COUNT(*) FROM `games` WHERE ((`home_score` < `away_score`) AND `away_team_id` = '.$team_id.'))) AS `wins`

I wonder if i can use this with some form of GROUP, or mySQL can know the $team_id itself? I've also tried some multiple JOINs with the 'team' table but they didn't work either.

Thanks,

Dan

Answer 1

Maybe this is what you are looking for?

SELECT all_wins.team_id, SUM(all_wins.wins)
FROM (
  SELECT 
     home_team_id as team_id, 
     SUM(IF(home_score > away_score,1,0)) as wins,
     SUM(home_score - away_score) as points
  FROM games
  GROUP BY home_team_id
  UNION ALL
  SELECT 
     away_team_id as team_id, 
     SUM(IF(away_score > home_score,1,0)) as wins,
     SUM(away_score - home_score) as points
  FROM games
  GROUP BY away_team_id
) all_wins
GROUP BY all_wins.team_id
ORDER BY SUM(all_wins.wins), SUM(all_wins.points)

ETA: Original answer wasn't complete, I think this should be better.

The inner two queries that are UNION'd together are getting the home and away wins for each team. The outer query simply sums up the home and away wins for the total win count.

Answer 2

Let's do it step by step:

Select the won games at home and the score at home:

   SELECT COUNT(*) as wins, SUM(G.home_score) as score FROM games G WHERE 
      G.team_id = T.team_id #See 3. query and you'll understand
      G.home_score > away_score

Let's call this result HOME_GAMES.

Select the won games and the score of away games:

SELECT COUNT(*) as wins, SUM(G.away_score) as score FROM games G
WHERE 
  G.team_id = T.team_id #See 3. query and you'll understand
  G.away_score > G.home_score

Let's call this result AWAY_GAMES.

Select the total won games and the total score:

   SELECT (A.wins + H.wins) AS total_wins, (A.score + H.score) AS total_score FROM
   (AWAY_GAMES) AS A, (HOME_GAMES) AS H, teams T 
   ORDER BY total_wins, total_score

==> Put all together by substituting AWAY_GAMES and HOME_GAMES:

SELECT (A.wins + H.wins) AS total_wins, (A.score + H.score) AS total_score FROM 
  (SELECT COUNT(*) as wins, SUM(G.away_score) as score FROM games G
   WHERE 
     G.team_id = T.team_id #See 3. and you'll understand
     G.away_score > G.home_score) AS A, 

   (SELECT COUNT(*) as wins, SUM(G.home_score) as score FROM games G 
   WHERE 
      G.team_id = T.team_id #See 3. and you'll understand
      G.home_score > away_score) AS H, 

   teams T
   ORDER BY total_wins, total_score 

Answer 3

Based on Eric's solution - here is my final query if anyone else has a similar problem - thanks for everyones help.

SELECT `teams`.`id`, `teams`.`name`,
        SUM(`all_wins`.`gp`) AS `gp`,
        SUM(`all_wins`.`w`) AS `w`, SUM(`all_wins`.`l`) AS `l`, SUM(`all_wins`.`t`) AS `t`,
        SUM(`all_wins`.`ptf`) AS `ptf`, SUM(`all_wins`.`pta`) AS `pta`
FROM (
  SELECT
     `home_team_id` as `team_id`,
     COUNT(`home_score`) AS `gp`,
     SUM(IF(`home_score` > `away_score`,1,0)) as `w`,
     SUM(IF(`home_score` < `away_score`,1,0)) as `l`,
     SUM(IF(`home_score` = `away_score`,1,0)) as `t`,
     SUM(IFNULL(`home_score`,0)) as `ptf`,
     SUM(IFNULL(`away_score`,0)) as `pta`
    FROM `games`    
  GROUP BY `home_team_id`
  UNION ALL
  SELECT
     `away_team_id` as `team_id`,
     COUNT(`home_score`) AS `gp`,
     SUM(IF(`away_score` > `home_score`,1,0)) as `w`,
     SUM(IF(`away_score` < `home_score`,1,0)) as `l`,
     SUM(IF(`away_score` = `home_score`,1,0)) as `t`,
     SUM(IFNULL(`away_score`,0)) as `ptf`,
     SUM(IFNULL(`home_score`,0)) as `pta`
    FROM `games`

  GROUP BY `away_team_id`
) `all_wins`
LEFT JOIN `teams` ON `all_wins`.`team_id` = `teams`.`id`
GROUP BY `all_wins`.`team_id`
ORDER BY SUM(`all_wins`.`w`) DESC, SUM(`all_wins`.`ptf`) DESC