Search code examples
pythonsqlpgadmin-4

SQL query wont run because of a strange group by Error

I wrote this SQL query in the python interface using the PGadmin4 SQL server.

It is supposed to return an ordered (descending by sum and if equal ascending by stadiumID) where the sum is the number of goals that got scored in the stadium. if a stadium didn't have any matches in it the sum should be 0.

The game table has a record of all the matches, in which stadium they happened and how many people were in the crowd

The stadium table has the number of the stadium the capacity and the team that owns it

The score table has the match that the goal was in, the player who scored, and how many goals he scored

Database:

game stadium score

query = sql.SQL("
    SELECT T.Stadiumid AS Stadiumid, T.SUM(Amount) AS Sum 
    FROM(
        (SELECT game.Stadiumid AS Stadiumid,SUM(Amount) AS Sum 
         FROM game,score 
         WHERE (game.Matchid=score.Matchid) 
         )
         UNION 
         (SELECT stadium.Stadiumid AS unplayedStadiumid,0 AS unplayedSum
          FROM stadium 
          WHERE (stadium.Stadiumid NOT IN (SELECT game.Stadiumid FROM game))
         )
         ) AS T 
    GROUP BY T.Stadiumid
    ORDER BY T.Sum DESC,T.Stadiumid
    ")

When executing the query I get the following error message:

column "game.stadiumid" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: ...d AS Stadiumid, T.SUM(Amount) AS Sum FROM((SELECT game.Stadi...

What seems to be the problem?

Solution

  • I believe there is a missing GROUP BY in your first internal select. There, you are looking for SUM(Amount), but you do not specify over which variables it should be summed.

    If you want to sum over all the game.Stadiumid, then this code works for your first part of the Union:

    SELECT game.Stadiumid AS Stadiumid,SUM(Amount) AS Sum 
                      FROM game,score 
                      WHERE (game.Matchid=score.Matchid)
                      GROUP BY game.Stadiumid