SQL query wont run because of a strange group by error-CodePudding

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

It is sepose to return an ordered (decending by sum and if equal ascending by stadiumID) where 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?

CodePudding user response：

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

CodePudding user response：

You've missed group by in this subquery

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