title: sql for calculating points for games
body: <p>I have a table with football matches.</p>
  
  <p>Table games:</p>
  
  <pre><code>id
  team_home (varchar)
  team_away (varchar)
  team_home_goals INT
  team_away_goals INT
  </code></pre>
  
  <p>To insert I do like this:</p>
  
  <pre><code>insert into games values(null, 'Liverpool', 'Chelsea', 0, 0);
  insert into games values(null, 'Arsenal', 'City', 1, 2);
  insert into games values(null, 'ManUTD', 'Tottenham', 2, 1);
  
  insert into games values(null, 'Chelsea', 'Arsenal', 0, 0);
  insert into games values(null, 'Tottenham', 'Liverpool', 1, 0);
  insert into games values(null, 'City', 'ManUTD', 2, 1);
  </code></pre>
  
  <p>I need to create a query to get the table with positions.
  According to the inserts above the table would be:</p>
  
  <pre><code>1 - City 6 points  
  2 - ManUTD 3 points
  3 - Tottenham 3 points
  4 - Chelsea 2 points
  5 - Liverpool 1 point
  6 - Arsenal 1 point
  </code></pre>
  
  <p>The query I create for this is either for "home teams" or "away teams".
  I could run this query twice and then sum the points some where else. But I wonder if there is a smart way to 
  do everything in only one query. This is what I have now (I run this twice, first for home team and then for away team):</p>
  
  <pre><code>select 
  team_home,
  sum
  (
  case 
  when team_home_goals &gt; team_away_goals then 3
  when team_home_goals = team_away_goals then 1
  when team_home_goals &lt; team_away_goals then 0
  end
  )
  as points
  from 
  games
  group by team_home;
  </code></pre>