How to get the count of 2 values in the same column, in the same table?

I have a dataset about chess games, and in the winner column there are only 2 values: ‘white’ and ‘black’. I want to show COUNT(winner) as WHITE_WINS and COUNT(winner) as BLACK_WINS. I tried inner joining the table by itself, however couldn’t get the correct SQL query to do the job. How can I get a table like:

WHITE_WINS : 5986
BLACK_WINS : 5893

Answer

Use conditional aggregation:

SELECT
    COUNT(CASE WHEN winner = 'white' THEN 1 END) AS WHITE_WINS,
    COUNT(CASE WHEN winner = 'black' THEN 1 END) AS BLACK_WINS
FROM yourTable;