View created from valid SQL statement returns error "#1242 - Subquery returns more than 1 row"

Question

When I run the below SQL statement, it correctly shows me what I expect:

 SELECT Users.idQuiz as ThisIDQuiz,Rounds.RoundNr as ThisRoundNr, Questions.QuestionNr as ThisQuestionNr, Answer as ThisAnswer, Questions.QuestionScore AS QuestionScoreMax,AnswerCorrect,
(SELECT COUNT(*) as "Aantal Ploegen met dit antwoord"
FROM `Answers`
JOIN Questions on Answers.idQuestion = Questions.idQuestion
JOIN Rounds on Questions.idRound = Rounds.idRound
JOIN Users on Users.idUser = Answers.idUser
 where (Users.idQuiz = ThisIDQuiz AND Rounds.RoundNr = ThisRoundNr AND Questions.QuestionNr=ThisQuestionNr AND Answers.Answer = ThisAnswer )
GROUP BY Users.idQuiz,Rounds.RoundNr, Questions.QuestionNr,Answer
) as NrOfTeamsWithThisAnswer,
(SELECT COUNT(*)
 FROM Users
 WHERE ((Users.idQuiz = ThisIDQuiz) AND (Users.UserType = 0))
 ) As TotalNrOfTeams,
 AnswerCorrect *((Select TotalNrOfTeams)- (SELECT NrOfTeamsWithThisAnswer))as ScoreForThisAnswer
FROM `Answers`
JOIN Questions on Answers.idQuestion = Questions.idQuestion
JOIN Rounds on Questions.idRound = Rounds.idRound
JOIN Users on Users.idUser = Answers.idUser
WHERE Questions.QuestionType = 5
GROUP BY ThisAnswer
ORDER BY ThisIDQuiz, ThisRoundNr, ThisQuestionNr, ThisAnswer;

See Results of the query for what the result looks like.

I then create a VIEW from this statement. The view is created fine, but when I open it, I get the error "#1242 - Subquery returns more than 1 row".

I tried dropping and recreating the view, same result. When I use the exact same SQL statement but without the penultimate line (GROUP BY ThisAnswer), it works fine (i.e. I can create the view and it opens without an error). This second view suits my purposes fine, so I can continue, but just out of curiosity: can someone explain this behaviour?

I use phpMyAdmin version 5.1.3 to do my SQL manipulations.

Answer 1

As @NickW asks, the statement GROUP BY ThisAnswer expects you to have an aggregate function (i.e. count, avg, max, min, etc.) somewhere in that main SELECT. Having a GROUP BY without an aggregate function will create errors. Either add the aggregate function, or remove the GROUP BY statement in the main (outermost) SELECT.