Question 379552 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Combining two sql columns into one and counting the number of unique values?

I have two postgresql tables with the following data

[teams]
[id][team_name] 
 1   team 1
 2   team 2
 3   team 3

[matches]
[participant 1][participant 2]
 1              3
 2              1
 3              2

The matches table references the team ID.

I am trying to count the number of times each team has played a match, so that it is possible to display the total number of games each team has played. I have unsuccessfully attempted to do this for hours with UNIONS, COUNT etc. Would appreciate any assistance to tackle this.

Tags:

2

Answers


  1. 0

    As you can see, each record within matches is actually two records: participant 1
    has a home match (let it be kind = 1) while participant 2 has a guest match (kind = 2).
    So far so good, let’s duplicate each record (with a help of cte) and then we can use good old count and group by

    with games as (
  select [participant 1] id,
         1 kind -- home matches
    from Matches

   union all

  select [participant 2] id,
         2 kind -- guest matches
    from Matches
) 

  select t.team_name,
         count(1) total_matches
    from games g 
    join teams t on (t.id = g.id)
group by t.id,
         t.team_name
order by t.team_name
    Login or Signup to reply.
  2. 0

    I would go with the following solution:

    SELECT COUNT(DISTINCT combined_team) AS unique_teams_count
FROM (
    SELECT team_name AS combined_team FROM teams
    UNION
    SELECT team_name FROM teams JOIN matches ON teams.id = matches.participant_1
    UNION
    SELECT team_name FROM teams JOIN matches ON teams.id = matches.participant_2
) AS combined_teams;

    The COUNT() function is used to count the number of unique team names. The DISTINCT keyword ensures that only unique values are counted by the COUNT() function. The UNION operator is used to combine the team names from different sources – directly from the teams table and via joins with the matches table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search