Postgresql – Column does not exist in UNION ALL query

postgresql

I am doing some code for my badminton club.

Tables

CREATE TABLE School (
    Name text PRIMARY KEY
);

CREATE TABLE Tournament (
    Number integer PRIMARY KEY
);

CREATE TABLE Player (
    Name text NOT NULL,
    SchoolName text NOT NULL,
    Gender text CHECK (Gender IN ('Masculin', 'Féminin'))
);

ALTER TABLE ONLY Player
    ADD CONSTRAINT Player_pkey PRIMARY KEY (name, SchoolName);

ALTER TABLE ONLY Player
    ADD CONSTRAINT SchoolName_fkey FOREIGN KEY (SchoolName) REFERENCES School(Name);

CREATE TABLE Result (
    -- PlayerId_fkey text
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text CHECK (Category IN ('Benjamin', 'Cadet', 'Juvénile')),
    TournamentNumber integer,
    Score integer,
    TypeOfPlay text CHECK (TypeOfPlay IN ('Simple', 'Double'))
);

ALTER TABLE ONLY Result
    ADD CONSTRAINT PlayerName_fkey FOREIGN KEY (PlayerName_fkey, SchoolName_fkey) REFERENCES Player(Name, SchoolName);    

ALTER TABLE ONLY Result
    ADD CONSTRAINT TournamentNumber_fkey FOREIGN KEY (TournamentNumber) REFERENCES Tournament(Number);

Query

-- Select each player with their single and double total score
SELECT PlayerName_fkey, ScoreSimple, ScoreDouble

FROM ( 
    -- Select the sum of the best results for each player in single play
    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='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates 
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4 
        GROUP BY PlayerName_fkey, score, ScoreRank 

    UNION ALL 

    -- Select the sum of the best results for each player in double play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble 
    FROM (
        -- Select the best four results in the season for each player in double 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 double play
            SELECT PlayerName_fkey, score, TournamentNumber 
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey 
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates 
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4 
        GROUP BY PlayerName_fkey, score, ScoreRank) t1

Question

The problem I have is I get the error: "column "scoredouble" does not exist". How do I get the scoredouble column display on my first query? I cannot use a JOIN because some players have scores in one type of play (singles or double) and not the other.

Link here: http://rextester.com/XIG14273

Best Answer

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 > 0 ) tBestIndividualScoresWithDuplicates 
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 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 > 0 ) tBestIndividualScoresWithDuplicates 
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 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.

Related Question