CODEWITHSUNDEEP
mysqlsqldatabasegroup-by
user1701484
user1701484

Reputation: 233

GROUP BY clause is not displaying rows correctly

I have an "Answer" database table below:

Answer Table

AnswerId SessionId QuestionId Answer
10       AAD       7          A
11       AAD       7          B
12       AAD       7          D
13       AAA       1          A
14       AAC       1          True

Now as you can see above there are 3 answers for question 7 in Exam (Session) AAD, there is 1 answer for question 1 in Exam (Session) AAA, and these is 1 answer for question 1 in Exam (Session) AAC.

So When I output the table in php/html, it should display it like this:

  Question  Answer

  7         ABD
  1         A
  1         True

But the problem is that it is displaying the table in 3 separate rows which I do not want:

Question  Answer
7         ABD
7         ABD
7         ABD
1         ATrue
1         ATrue

So I am guessing that I have got my GROUP BY CLAUSE incorrect, my question is that what should the GROUP BY Clause be so that it is correct?

 SELECT DISTINCT an.SessionId, an.QuestionId, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( an.Answer
ORDER BY an.Answer
SEPARATOR  ' ' ) AS Answer, r.ReplyType, q.QuestionMarks
FROM Answer an
INNER JOIN Question q ON q.QuestionId = an.QuestionId
JOIN Reply r ON q.ReplyId = r.ReplyId
JOIN Option_Table o ON q.OptionId = o.OptionId
GROUP BY an.SessionId, an.QuestionId

Below I have included the SHOW CREATE TABLES for "Question" and "Answer" Tables:

Question Table:

 CREATE TABLE `Question` (
     `SessionId` varchar(10) NOT NULL,
     `QuestionId` int(10) NOT NULL,
     `QuestionContent` varchar(5000) NOT NULL,
     `NoofAnswers` int(2) NOT NULL,
     `ReplyId` int(1) NOT NULL,
     `QuestionMarks` int(4) NOT NULL,
     `OptionId` int(2) NOT NULL,
     PRIMARY KEY (`SessionId`,`QuestionId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer Table:

CREATE TABLE `Answer` (
 `AnswerId` int(10) NOT NULL AUTO_INCREMENT,
 `SessionId` varchar(10) NOT NULL,
 `QuestionId` int(10) NOT NULL,
 `Answer` varchar(5) NOT NULL,
 PRIMARY KEY (`AnswerId`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

Below is the "Question" Table rows (I only include SessionId and QuestionId columns as they are relevant for this situation:

SessionId QuestionId 
AAA       1
AAC       1
AAD       7

The rows in the "Answer" table is at top of this question.

UPDATE: With the query above I get these results (Only including relevant columns):

SessionId  QuestionId  Answer
AAA        1           A A        // This row answer should only be "A"
AAC        1           True True  //This row answer should only be "True"
AAD        7           A B D      //This row is fine

Upvotes: 0

Views: 99

Answers (1)

John Woo
John Woo

Reputation: 263723

You need to add GROUP BY clause. Try this, (it's a simplified version)

SELECT QuestionID, GROUP_CONCAT(Answer SEPARATOR '')
FROM tableName
GROUP BY SessionID, QuestionID

SQLFiddle Demo

Upvotes: 1

Related Questions