perform a calculation where data for calculation is two tables away

user_613605 0
Viewed 23

I am working in Postgres and have three tables, these are: matches, teams and results. I've created a DbFiddle too.

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
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
group by t.club;

And that gives me:

team_name | total_number_of_matches
----------+--------------------------
Vinica    | 3
Kolpa     | 2
Crnomelj  | 3

What I want to get to is:

team_name | total_number_of_matches | matches_won | matches_draw | matches_lost
----------+-------------------------+-------------+--------------+--------------
Vinica    | 3                       | 3           | 0            | 0
Kolpa     | 2                       | 0           | 1            | 1
Crnomelj  | 3                       | 0           | 1            | 2

I'm stuck on using the data from the results table and tying it with my query.

postgresql sql
1 Answers

What about the following 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
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;

It joins the result table and uses three sum functions with a case statement inside which represents the logic wether a game was won or lost.

user_74728190
Related