Calculating score for questionnaire

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating score for questionnaire

Author

Topic

davebatt
Starting Member

17 Posts

Posted - 2010-05-06 : 09:02:13

I have been building a questionnaire application and am about to calculate the results.

I store the answer given by the user like this:

The given answerid is put in a userAnswers table with a boolean indicating if correct

I firstly get a count of how many questions have been answered, and then was going to get a count of how many were correct and do something like:

(correctanswers / totalquestions) * 100)

However i have some questions that have multiple correct answers and so the useranswers table with have multiple records for this answer.

Basically I need to find out how many answers should have been given (this is stored in the table that holds the questions or I could do a count) and ensure for that question a corresponding number of right answers were given.

All correct answers have to be given, and if just one wrong answer is given for a question it is wrong.

Can someone give me a clue because I would like to do this last part in one statement and im having trouble.

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 09:17:09

Tables could look like this :
QUESTIONS (Question_ID,Question)
ANSWERS (Answer_ID, Question_ID,Answer,Is_Right)
UserAnswers(User_ID, Question_ID, Answer_ID, Is_Right)

next you can ask
SELECT Question_ID, (CASE WHEN GoodAnswers = AllAnswers THEN 1 ELSE 0 END) IsAnswerGood
FROM (
select
Question_ID,
(select count(*) from UserAnswers ua JOIN Answers a on a.answer_id = ua.answer_id and ua.Is_Right = a.Is_Right where User_ID = xxxxx and a.Question_id = q.Question_id ) GoodAnswers,
(select count(*) from Answers a where a.Question_id = q.Question_id ) AllAnswers
from Questions q
) Quest

apodemus

davebatt
Starting Member

17 Posts

Posted - 2010-05-06 : 09:24:45

Thanks for your speedy reply, I came up with this while it was waiting (you can probably see sql isnt my strongest suit)

SELECT COUNT(slides.slideID) AS counter, slides.slideID, slides.correctAnswers
into #tempytable
FROM slides INNER JOIN
userAnswers ON slides.slideID = userAnswers.slideID
WHERE (userAnswers.resultSet = 1) AND (slides.type = 5)
GROUP BY slides.slideID, slides.correctAnswers

select count(*) from #tempytable where counter = correctanswers

Ill have a play with your solution though.

Thanks Dave

davebatt
Starting Member

17 Posts

Posted - 2010-05-06 : 09:26:32

sorry that should have been

WHERE (userAnswers.resultSet = 1) AND (slides.type = 5) and (userAnswers.correct = 1)

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 09:46:41

In the COUNTER column you will get 1 in each record, i dont know if its what you expect :)

apodemus

davebatt
Starting Member

17 Posts

Posted - 2010-05-06 : 10:52:12

Thanks apodemus, but im using the group by and its giving me exactly what I would expect.

Cheers mate