title: Value for new column calculated from previous columns
body: <p>I am working in Postgres and have three tables, these are: <code>matches</code>, <code>teams</code> and <code>results</code>. I have a <a href="https://www.db-fiddle.com/f/eeDQ7LZ8AJd611ihQs6NL2/5" rel="nofollow noreferrer">DbFiddle here</a>.</p>
  <pre><code>teams table
  
   id_team |      club      | shorthand
  ---------+----------------+-----------
         1 | Vinica         | VIN
         2 | Kolpa          | KOL
         3 | Crnomelj       | CRN
         4 | Klub Študentov | KBŠ
         5 | Zilje          | ZLJ
         6 | Preloka        | PRK
  </code></pre>
  <pre><code>matches table
  
   id_match | id_home | id_away |      date_time
  ----------+---------+---------+---------------------
          1 |       1 |       2 | 2018-11-03 16:30:00
          2 |       1 |       3 | 2019-11-03 16:30:00
          3 |       2 |       3 | 2019-11-03 16:30:00
          4 |       3 |       1 | 2020-11-03 16:30:00
  </code></pre>
  <pre><code>results table
  
   id_match | home | away
  ----------+------+------
          1 |    5 |    3
          2 |    2 |    0
          3 |    0 |    0
          4 |    1 |    3
  </code></pre>
  <p>I already have this query:</p>
  <pre><code>select t.club as team_name, count(*) as total_number_of_matches,
  sum(case when (t.id_team = m.id_home and r.home &gt; r.away) or (t.id_team = m.id_away and r.home &lt; r.away) then 1 else 0 end) as matches_won,
  sum(case when r.home = r.away then 1 else 0 end) as matches_draw,
  sum(case when (t.id_team = m.id_home and r.home &lt; r.away) or (t.id_team = m.id_away and r.home &gt; r.away) then 1 else 0 end) as matches_lost,
  sum(case when (t.id_team = m.id_home) then r.home when (t.id_team = m.id_away) then r.away end) as total_goals_scored,
  sum(case when (t.id_team = m.id_home) then r.away when (t.id_team = m.id_away) then r.home end) as total_goals_conceded
  from matches m cross join lateral
       (values (m.id_home), (m.id_away)
       ) v(id_team) join
       teams t
       on t.id_team = v.id_team
       join results r on m.id_match = r.id_match
  group by t.club;
  </code></pre>
  <p>And that gives me:</p>
  <pre><code>team_name | total_number_of_matches | matches_won | matches_draw | matches_lost | total_goals_scored | total_goals_conceded 
  ----------+-------------------------+-------------+--------------+--------------+--------------------+---------------------
  Vinica    | 3                       | 3           | 0            | 0            | 10                 | 4
  Kolpa     | 2                       | 0           | 1            | 1            | 3                  | 5
  Crnomelj  | 3                       | 0           | 1            | 2            | 1                  | 5
  </code></pre>
  <p>What I want to get to is:</p>
  <pre><code>team_name | total_number_of_matches | matches_won | matches_draw | matches_lost | total_goals_scored | total_goals_conceded | goal_difference | points
  ----------+-------------------------+-------------+--------------+--------------+--------------------+----------------------+-----------------+-------
  Vinica    | 3                       | 3           | 0            | 0            | 10                 | 4                    | 6               | 9
  Kolpa     | 2                       | 0           | 1            | 1            | 3                  | 5                    | -2              | 1
  Crnomelj  | 3                       | 0           | 1            | 2            | 1                  | 5                    | -4              | 1
  </code></pre>
  <p>I'm struggling with re-using values from previous columns.</p>
  <p><code>goal_difference</code> is a simple subtract calculation: <code>total_goals_scored - total_goals_conceded</code> and <code>points</code> is calculated from the <code>matches_won</code> and <code>matches_draw</code> columns. When I add <code>total_goals_scored - total_goals_conceded as goal_difference</code> to my query, it doesn't like it.</p>
  <blockquote>
  <p>Win = 3 points</p>
  </blockquote>
  <blockquote>
  <p>Draw = 1 point</p>
  </blockquote>
  <blockquote>
  <p>Loss = 0 points and thus can be ignored</p>
  </blockquote>