2988 - Campeonato Cearense

SELECT
  name,
  COUNT(name) AS matches,
  SUM(CAST(Vitorias AS int)) AS victories,
  SUM(CAST(Derrotas AS int)) AS defeats,
  SUM(CAST(Empate AS int)) AS draws,
  (SUM(CAST(Vitorias AS int)) * 3) + (SUM(CAST(Empate AS int)) * 1) AS score
FROM
  (
    SELECT
      teams.name,
      matches.team_1 AS ID,
      (matches.team_1_goals > matches.team_2_goals) AS Vitorias,
      (matches.team_1_goals < matches.team_2_goals) AS Derrotas,
      (matches.team_1_goals = matches.team_2_goals) AS Empate
    FROM
      teams
      INNER JOIN matches ON matches.team_1 = teams.id
    UNION ALL
    SELECT
      teams.name,
      matches.team_2 AS ID,
      (matches.team_2_goals > matches.team_1_goals) AS Vitorias,
      (matches.team_2_goals < matches.team_1_goals) AS Derrotas,
      (matches.team_2_goals = matches.team_1_goals) AS Empate
    FROM
      teams
      INNER JOIN matches ON matches.team_2 = teams.id
  ) AS tabela
GROUP BY
  name
ORDER BY
    score DESC;