Value for new column calculated from previous columns

user_613605 0
Viewed 18

I am working in Postgres and have three tables, these are: matches, teams and results. I have a DbFiddle here.

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

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

results table

 id_match | home | away
----------+------+------
        1 |    5 |    3
        2 |    2 |    0
        3 |    0 |    0
        4 |    1 |    3

I already have this query:

select t.club as team_name, count(*) as total_number_of_matches,
sum(case when (t.id_team = m.id_home and r.home > r.away) or (t.id_team = m.id_away and r.home < 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 < r.away) or (t.id_team = m.id_away and r.home > 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;

And that gives me:

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

What I want to get to is:

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

I'm struggling with re-using values from previous columns.

goal_difference is a simple subtract calculation: total_goals_scored - total_goals_conceded and points is calculated from the matches_won and matches_draw columns. When I add total_goals_scored - total_goals_conceded as goal_difference to my query, it doesn't like it.

Win = 3 points

Draw = 1 point

Loss = 0 points and thus can be ignored

postgresql sql
1 Answers

SQL does not allow you to re-use column aliases in the select where they are defined (nor in the from or where clauses either). You use a subquery, CTE, or lateral join to define them -- although the latter doesn't work for this purpose in an aggregation query.

In your case, you can simplify the expressions a bit by enhancing the values(). The following just adds an is_home flag and filter for the conditional aggregation:

select t.club as team_name,
       count(*) as total_number_of_matches,
       count(*) filter (where is_home and r.home > r.away or not is_home and r.away > r.home) as matches_won,
       count(*) filter (where r.home = r.away) as matches_draw,
       count(*) filter (where is_home and r.home < r.away or not is_home and r.away < r.home) as matches_lost,
       sum(r.home) filter (where is_home) + sum(r.away) filter (where not is_home) as total_goals_scored,
       sum(r.away) filter (where is_home) + sum(r.home) filter (where not is_home) as total_goals_conceded,
       sum(r.home - r.away) filter (where is_home) + sum(r.away - r.home) filter (where not is_home) as goal_difference,
   (3 * count(*) filter (where is_home and r.home > r.away or not is_home and r.away > r.home) +
    count(*) filter (where r.home = r.away) 
   ) as points
from public.matches m cross join lateral
     (values (m.id_home, true), (m.id_away, false)
     ) v(id_team, is_home) join
     public.teams t
     on t.id_team = v.id_team join
     public.results r
     on m.id_match = r.id_match
group by t.club;

Here is a db<>fiddle.

user_11440350
Related