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.
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 1Reply