Chess results to leader board-CodePudding

I've got a table containing results of chess matches:

id	player_white	player_black	result	session_start	session_end
1	Jonathan1	TrentX	0	2020-01-01 13:10:10	2020-01-01 13:15:23
2	TrentX	Jonathan1	1	2020-01-01 13:18:32	2020-01-01 13:23:13
3	Ezekiel2001	Jonathan1	1	2020-01-01 13:30:12	2020-01-01 13:37:01
4	Ezekiel2001	TrentX	3	2020-01-01 13:40:08	2020-01-01 13:44:02
5	Jonathan1	Ezekiel2001	4	2020-01-01 13:48:32	2020-01-01 13:53:56
6	TrentX	Ezekiel2001	1	2020-01-01 13:56:30	2020-01-01 13:59:02

result is an enum where:

0 = white victory
1 = black victory
2 = draw by agreement
3 = draw by stalemate
4 = draw by repetition
5 = draw by fifty move rule
6 = aborted

I would like to sort players by maximum number of victories and draws to display a leader board. Is there any straightforward way to get this leader board view or are any changes required in the way the data is organized?

CodePudding user response：

You can list the white and black victories along with draws and then apply group by on counts to get the result set. Please refer below.

SELECT player, count(1) number_of_victories_and_draws
FROM(
SELECT player_white player, result from plays pw_victory_draw_table where result IN( 0, 2, 3, 4, 5)
UNION ALL
SELECT player_black player, result  from plays pb_victory_draw_table where result IN (1, 2, 3, 4, 5)
) playes_union_table
GROUP BY player
ORDER BY 1;

Result:

Ezekiel2001 3
Jonathan1   4
TrentX  1

CodePudding user response：

"… changes required in the way the data is organized?"

Lacking normalization every condition requires sub-query :

SELECT
                        player_name,
    sum(player_wins) AS player_wins,
    sum(player_loss) AS player_loss,
    sum(player_draw) AS player_draw
FROM (

    SELECT
        player_white AS player_name,
        count(*)     AS player_wins,
        0            AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 0

    UNION ALL

    SELECT
        player_black AS player_name,
        count(*)     AS player_wins,
        0            AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 1

    UNION ALL

    SELECT
        player_white AS player_name,
        0            AS player_wins,
        count(*)     AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 1

    UNION ALL

    SELECT
        player_black AS player_name,
        0            AS player_wins,
        count(*)     AS player_loss,
        0            AS player_draw
    FROM
        tablename
    WHERE
        result = 0

    UNION ALL

    SELECT
        player_white AS player_name,
        0            AS player_wins,
        0            AS player_loss,
        count(*)     AS player_draw
    FROM
        table_name
    WHERE
        result BETWEEN 2 AND 5

    UNION ALL

    SELECT
        player_black AS player_name,
        0            AS player_wins,
        0            AS player_loss,
        count(*)     AS player_draw
    FROM
        table_name
    WHERE
        result BETWEEN 2 AND 5

)
GROUP BY
    player_name
ORDER BY
    player_wins DESC,
    player_loss  ASC,
    player_draw DESC,
    player_name  ASC
;