1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
| SELECT equipe ,
SUM(matchs_joues)as matchs_joues,
SUM(matchs_gagnes)as matchs_gagnes,
SUM(matchs_nuls)as matchs_nuls,
SUM(matchs_perdus)as matchs_perdus,
SUM(goals_for)as goals_for,
SUM(goals_against)as goals_against,
SUM(diff)as diff ,
SUM(t.points) as points from
(
SELECT home_team as equipe,
SUM(CASE
WHEN sc1=' ' THEN 0 ELSE 1
END) AS matchs_joues,
SUM(CASE
WHEN sc1 > sc2 THEN 1 ELSE 0
END) AS matchs_gagnes,
SUM(CASE
WHEN sc1=' ' or sc2=' ' THEN 0 ELSE 1
END) AS matchs_nuls,
SUM(CASE
WHEN sc1 < sc2 THEN 1 ELSE 0
END) AS matchs_perdus ,
SUM (CASE
when sc1=' ' then ' '
WHEN sc1 > sc2 THEN 3
WHEN sc1 = sc2 THEN 1
WHEN sc1 < sc2 THEN 0
END) points,
SUM (sc1 - sc2) as diff ,
SUM(sc1)as goals_for,
SUM(sc2)as goals_against
FROM MATCH2
where nom_pays=13
GROUP BY home_team
UNION
SELECT away_team as equipe,
SUM(CASE
WHEN sc2=' ' THEN 0 ELSE 1
END) AS matchs_joues,
SUM(CASE
WHEN sc2 > sc1 THEN 1 ELSE 0
END) AS matchs_gagnes,
SUM(CASE
WHEN sc2=' ' or sc1=' ' THEN 0 ELSE 1
END) AS matchs_nuls,
SUM(CASE
WHEN sc2 < sc1 THEN 1 ELSE 0
END) AS matchs_perdus ,
SUM (CASE
when sc2=' ' then ' '
WHEN sc2> sc1 THEN 3
WHEN sc2= sc1 THEN 1
WHEN sc2 < sc1 THEN 0
END) points,
SUM (sc2 - sc1) as diff ,
SUM(sc2)as goals_for,
SUM(sc1)as goals_against
FROM MATCH2
where nom_pays=13
GROUP BY away_team
) t
group by equipe
order by points DESC, diff DESC |