Count the number of times an id appears across two columns

user_613605 0
Viewed 64

I have a matches and a teams tables. Here's a Db Fiddle. I'm using Postgres.

They look like this:

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

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

I'm trying to query and join the two to get:

team name | total number of matches
----------+--------------------------
Vinica    | 3
Kolpa     | 2
Crnomelj  | 3

The hard bit that I find is the fact that the team id can appear in two columns of the matches table.

postgresql sql
1 Answers

You can unpivot the data using a lateral join. Then join and aggregate:

select t.club, count(*)
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;

Here is the fiddle.

user_11440350
Related