gistfile1.txt

SELECT
  user_id,
  SUM(win) AS won,
  SUM(draw) AS drawn,
  SUM(loss) AS lost,
  SUM(goals_for) AS goals_for,
  SUM(goals_against) AS goals_against,
  SUM(goals_for) - SUM(goals_against) AS goals_difference,
  player,
  username,
  community_result_id,
  COUNT(*) AS played,
  SUM(points) AS points
FROM (SELECT
  home_team_user_id AS user_id,
  CASE
    WHEN home_score > away_score THEN 1
    ELSE 0
  END AS win,
  CASE
    WHEN home_score = away_score THEN 1
    ELSE 0
  END AS draw,
  CASE
    WHEN home_score < away_score THEN 1
    ELSE 0
  END AS loss,
  CASE
    WHEN home_score > away_score THEN 3
    WHEN home_score = away_score THEN 1
    ELSE 0
  END points,
  home_score AS goals_for,
  away_score AS goals_against,
  users.name AS player,
  users.username,
  community_results.community_result_id
FROM community_results
LEFT JOIN users
  ON community_results.home_team_user_id = users.id
UNION ALL
SELECT
  away_team_user_id AS user_id,
  CASE
    WHEN away_score > home_score THEN 1
    ELSE 0
  END AS win,
  CASE
    WHEN away_score = home_score THEN 1
    ELSE 0
  END AS draw,
  CASE
    WHEN away_score < home_score THEN 1
    ELSE 0
  END AS loss,
  CASE
    WHEN home_score > away_score THEN 3
    WHEN home_score = away_score THEN 1
    ELSE 0
  END points,
  away_score AS goals_for,
  home_score AS goals_against,
  users.name AS player,
  users.username,
  community_results.community_result_id
FROM community_results
LEFT JOIN users
  ON community_results.away_team_user_id = users.id) t
WHERE user_id = 1
GROUP BY user_id
ORDER BY won DESC
LIMIT 1;