Column does not exist in UNION ALL query
With the help of many people whom gave me a lot of help, I finally got a working solution:

SELECT PlayerName_fkey, SumScoresSimpleTotal, SumScoresDoubleTotal, SumScoresTotal, rank() over (order by SumScoresTotal desc) AS OverallRank 

FROM (

    SELECT COALESCE(t1.PlayerName_fkey, t2.PlayerName_fkey) as PlayerName_fkey, coalesce(SumScoresSimple, 0) as SumScoresSimpleTotal, coalesce(SumScoresDouble,0) as SumScoresDoubleTotal, 
    (coalesce(SumScoresSimple, 0) + coalesce(SumScoresDouble, 0)) as SumScoresTotal 
    FROM (

    SELECT PlayerName_fkey, ScoreSimple, sum(ScoreSimple) over (partition by PlayerName_fkey) as SumScoresSimple

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple 
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank 
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber 
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey 
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score &gt; 0 ) tBestIndividualScoresWithDuplicates 
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank &lt;= 4 
        GROUP BY PlayerName_fkey, score, ScoreRank)tinside1) t1

        FULL OUTER JOIN

    (SELECT PlayerName_fkey, ScoreDouble, sum(ScoreDouble) over (partition by PlayerName_fkey) as SumScoresDouble

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank 
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber 
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey 
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Double' AND score &gt; 0 ) tBestIndividualScoresWithDuplicates 
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank &lt;= 4 
        GROUP BY PlayerName_fkey, score, ScoreRank) tinside2) t2

    ON t1.PlayerName_fkey = t2.PlayerName_fkey

    GROUP BY t1.PlayerName_fkey, t2.PlayerName_fkey, SumScoresSimple, SumScoresDouble

    ORDER BY SumScoresTotal DESC


The colasesce functions are particularly important here to avoid empty entries for both the player names and the scores.