MSSQL Coding Problem

I have a table (of sports results) which has several fields but two fields for names of players - PlayerA and PlayerB, as the play together in a team. I want to count how many times each player actually plays as they may play with other people in other matches. For example, Joe may play 5 times as PlayerA and 3 times as PlayerB.

I've tried a UNION which can list each player in the same column with a count of how many times they've played, but the problem is the same player is often listed twice - once when he plays in the PlayerA position and again in PlayerB position. In the above example Joe appears once with 5 appearances and once (lower down the list) with 3 appearances.

How can I get one view of all the Players without the same name appearing twice?

 

Any thoughts would be helpful.

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Barry... if you UNION ALL in a sub-query you can GROUP BY `player` in your outer query to get an aggregate count across either player slot (A or B). 

    Here is a simple example that (I think) aligns with the way you've got your data structured.

    Sample data - using single letters to denote different player names ('A','B','C', or 'D')

    match,playerA,playerB
    1,A,B
    2,A,C
    3,B,A
    4,A,D
    5,D,B
    6,A,C
    7,B,D
    8,C,B
    9,A,D
    10,B,A

    SQL example (using StandardSQL in bigquery)

    #standardSQL
    SELECT player
    , SUM(matches) as matches
    
    FROM (
        (
          SELECT playerA as player
          , COUNT(*) as matches
          FROM `cc_sandbox.players`
          GROUP BY 1
        )
        UNION ALL
        (
          SELECT playerB as player
          , COUNT(*) as matches
          FROM `cc_sandbox.players`
          GROUP BY 1
        )
    )
    GROUP BY 1
    Reply Like
  • Many many thanks, I should brush up on my SQL skills, I'd ferried around something similar with no success but the example looks perfect.

    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 12 days agoLast active
  • 2Replies
  • 95Views
  • 2 Following

Create A New Discussion

Share your thoughts