Counting values from multiple columns in SQL

Question

For a sports tournament, where people play either in pairs against another team of 2 players, I want to output a list of each individual opponent faced by a specific player, as well as the number of wins, draws and losses in games between the specific player and that opponent.

For example, if the results were:

Player 1 & Player 2 2-1 Player 3 & Player 4
Player 3 & Player 1 1-0 Player 2 & Player 4
Player 2 & Player 4 4-2 Player 1 & Player 3
Player 1 & Player 4 0-0 Player 3 & Player 2

I would want the output for Player 1 to be:

Player 2, W1 D1 L1
Player 3, W1 D1 L0
Player 4, W2 D0 L1

This has proven surprisingly difficult to achieve. My data is organised into two tables, a table of all the players (Players) and a table of scores (Scores).

Players has fields PlayerID and PlayerName.

Scores has fields Player1, Player2, Player3, Player4 and Score, where each of the Player fields can contain any PlayerID (so any given ID could appear in different columns depending on the row), and the Score is stored such that if it less than 21, Player 1 & 2 lose, and if it is greater than 21, they win.

What is wrong with this query? Apparently it's returning 0.

SELECT opponent as opp, COUNT(result='win') as c1, COUNT(result='draw') as c2, COUNT(result='loss') as c3
      FROM (SELECT
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) THEN Players3.PlayerName
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) THEN Players1.PlayerName
                ELSE 'NA' 
                END as opponent,
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) 
                THEN
                    CASE
                        WHEN Scores.Score > 21 THEN 'win'
                        ELSE 'NA'
                        END as result,
                    CASE
                        WHEN Scores.Score < 21 THEN 'loss'
                        ELSE 'NA'
                        END as result,
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) 
                THEN
                    CASE
                        WHEN Scores.Score < 21 THEN 'win'
                        ELSE 'NA'
                        END as result,
                    CASE
                        WHEN Scores.Score > 21 THEN 'loss'
                        ELSE 'NA'
                        END as result,
                WHEN Scores.Score = 21 THEN 'draw'
                ELSE 'NA' 
                END as result,
        FROM Scores
        INNER JOIN Players Players1 ON Scores.Player1=Players1.PlayerID
        INNER JOIN Players Players2 ON Scores.Player2=Players2.PlayerID
        INNER JOIN Players Players3 ON Scores.Player3=Players3.PlayerID
        INNER JOIN Players Players4 ON Scores.Player4=Players4.PlayerID
        WHERE Players1.PlayerName = '$player' OR Players2.PlayerName = '$player' OR Players3.PlayerName = '$player' OR Players4.PlayerName = '$player'
        UNION ALL
        SELECT
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) THEN Players4.PlayerName
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) THEN Players2.PlayerName
                ELSE 'NA' 
                END as opponent,
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) AND Scores.Score > 21 THEN 'win'
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) AND Scores.Score < 21 THEN 'loss'
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) AND Scores.Score < 21 THEN 'win'
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) AND Scores.Score > 21 THEN 'loss'
                WHEN Scores.Score = 21 THEN 'draw'
                ELSE 'NA' 
                END as result,
        FROM Scores
        INNER JOIN Players Players1 ON Scores.Player1=Players1.PlayerID
        INNER JOIN Players Players2 ON Scores.Player2=Players2.PlayerID
        INNER JOIN Players Players3 ON Scores.Player3=Players3.PlayerID
        INNER JOIN Players Players4 ON Scores.Player4=Players4.PlayerID 
        WHERE Players1.PlayerName = '$player' OR Players2.PlayerName = '$player' OR Players3.PlayerName = '$player' OR Players4.PlayerName = '$player'
    ) AS SUBQUERY
    GROUP BY opp

Unfortunately, the DB isn't mine, and there are a bunch of other historic queries that rely on it retaining its current schema. I could probably create a copy somewhere else with different layout, but that seems needlessly wasteful, particularly given the database's size. — Leonard Terbant
– Leonard Terbant, Commented Mar 26, 2015 at 18:29

romanpilot · Accepted Answer · 2015-03-26 19:47:35Z

It isn't pretty, but since MySQL lacks a Pivot function, you could jackhammer the results with an inner query that breaks down who played whom and what the result was. Then the outer query could aggregate those results. Something like the following worked for me, although, again, not pretty:

select players.name as playerName, 
  opponents.name as opponentName, 
  SUM(CASE WHEN result = 'W'THEN 1 ELSE 0 END) AS win, 
  SUM(CASE WHEN result = 'L'THEN 1 ELSE 0 END) AS lose, 
  SUM(CASE WHEN result = 'D' THEN 1 ELSE 0 END) AS draw 
from  (
    select player1 as player, player3 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player1 as player, player4 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player2 as player, player3 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player2 as player, player4 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    -- now reverse result because opponents are being slotted into the player position
    union all
    select player3 as player, player1 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player4 as player, player1 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player3 as player, player2 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player4 as player, player2 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
) resultsByPlayer
inner join players on players.playerId = resultsByPlayer.player
inner join players as opponents on opponents.playerId = resultsByPlayer.opponent
group by players.playerId, opponents.playerId

Collectives™ on Stack Overflow

Counting values from multiple columns in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related