Difficult football league Dynamic order in MySQL?

Question

I have a table of games for the 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 IDs ordered by Wins (then indicates if possible)?

-

I have this query that works fine when I have $ team_id, but because of the reason, then I can only do one team at a time, and this does not allow ordering at the 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 $ team_id itself? I also tried several multiple JOINs with the "team" table, but they didn't work either.

Thanks,

Dan

Answer 1

Perhaps 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: the original answer was incomplete, I think it should be better.

The internal two queries, which are UNION'd together, receive winnings for each team. An external request simply sums the winnings of the house and guests for the total number of wins.

Answer 2

Take it step by step:

Select your won home games and home score:

  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 me call this result HOME_GAMES.

Select your won games and guest games score:

 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 me call this result AWAY_GAMES.

Select total games won and 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 

==> Combine everything together by replacing 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 decision - here is my final request, if anyone has a similar problem - thanks for helping everyone.

 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