title: SQL phpMyAdmin: List teams with more away wins than home wins
body: <p>I have a database called Football with the following tables: Footballers, Teams, Managers, Matches. I'm trying to list the teams that have more away wins than home wins. A home win is where a team is the home team in a match and the number of home goals exceeds the number of away goals – the opposite being the case for away wins.</p>
  
  <p>I can display the number of home wins using: </p>
  
  <pre><code>SELECT T.TeamName, COUNT(*) AS HomeWins
  FROM Teams T, Matches M
  WHERE M.HomeTeamID = T.TeamID AND M.HomeGoals &gt; M.AwayGoals
  </code></pre>
  
  <p>and similarly get the number of away wins.</p>
  
  <p>When I try to put the two together to get the teams with more away than home wins I'm getting an error, I've tried a few different ways including:</p>
  
  <pre><code>SELECT T.TeamName
  FROM Teams T, Matches M
  WHERE M.HomeTeamID = T.TeamID
  HAVING COUNT (M.HomeGoals &gt; M.AwayGoals) &gt; COUNT(M.AwayGoals &gt; M.HomeGoals)
  
  SELECT T.TeamName
  FROM Teams T, Matches M
  WHERE M.HomeTeamID = T.TeamID AND (COUNT (M.HomeGoals &gt; M.AwayGoals) &gt; COUNT(M.AwayGoals &gt; M.HomeGoals)
  
  SELECT T.TeamName
  WHERE
    (COUNT DISTINCT(TeamID) FROM Teams T, Matches M WHERE M.HomeTeamID = T.TeamID AND M.HomeGoals &gt; M.AwayGoals) &gt;
    (COUNT DISTINCT(TeamID) FROM Teams T, Matches M WHERE M.AwayTeamID = T.TeamID AND M.HomeGoals &lt; M.AwayGoals)
  </code></pre>
  
  <p>None of these are working. I know I'm probably missing something very basic here but I'd really appreciate some insight as I can't seem to get this to work.</p>