Maths Operation On Result From 2 Subqueries
-
I have two tables of data, one with a list of questions in, and another
with attempts at answering the questions. Below is a slightly
simplified version, but its gets the point across.
Questions
-------------------------------------------------------------
Id Text
-------------------------------------------------------------
1 What is my name?
2 What is my favourite colour?
3 What colour are my eyes
4 What is my middle name?
QuestionHistory
------------------------------------------------------------------------------
ID assessment_question_id answered correct
------------------------------------------------------------------------------
800 1 1 1
801 1 1 0
802 1 0 0
803 2 1 0
804 3 0 0
805 4 1 1
NB: People can choose to not answer ('pass') questions, hence the 3rd
column.
What I want to do is display a list of question text, along with the
number of times a question has been answered, how many times it has
been correctly answered, and the % answered correctly.
Now I've attempted it, and got the number of times answered/correctly
answered okay. However, I'm having problems getting the percentage I
know its answered correctly/answered, but how would I do this in T-SQL.
Here is what I have so far:
SELECT id, text,
--Attempted
(SELECT count(*)
FROM QuestionHistory
WHERE answered = 1 AND assessment_question_id = Questions.ID
GROUP BY assessment_question_id) as Attempted,
--Answered Correctly
(SELECT count(*)
FROM QuestionHistory
WHERE correct = 1 AND assessment_question_id = Questions.ID
GROUP BY assessment_question_id) as Correct
--Percentage ?
from Questions
So my two questions are:
1. Am I going the right way? And if no, is there a better way of trying
to achieve what i want?
2. How do I get a %, without simply duplicating the already created
subqueries and slapping a division operator in between!
Any help would be much appreciated.
Thanks
Steve
-
SELECT Text, SUM(answered ) as answered , SUM(correct) as correct, SUM(correct)*100
/SUM(answered ) as Percent
FROM QuestionHistory
INNER JOIN Questions ON assessment_question_id = Questions.ID
GROUP BY Text
_____________
Code for TallyGenerator -
I would add a case statement to Sergiy's query to handle possible division by zero.
SELECT Text, SUM(answered ) as answered , SUM(correct) as correct, (CASE WHEN SUM(answered) = 0 THEN NULL ELSE SUM(correct)*100 /SUM(answered ) END) as Percent FROM QuestionHistory INNER JOIN Questions ON assessment_question_id = Questions.ID GROUP BY TextYou can change NULL to 0 if you want (and it is safe) to assume that the percentage of correct answers is 0 when no one has answered a question.
--Andrew
-
Hi,
Use the below mentioned query to solve your peoblem..
SELECT qid,ques_text,
(select sum(ques_ans) from QUESTIONHISTORY
where q.qid=asst_qid
)Ques_Answered,
(select sum(ques_corr) from QUESTIONHISTORY
where q.qid=asst_qid
)Ques_Corrected,
(select isnull(convert(int,sum(ques_corr) * 100 /CONVERT(NUMERIC(18,2),sum(ques_ans))),0) from QUESTIONHISTORY
where q.qid=asst_qid
having sum(ques_ans) <> 0
)Percentage_Ques
FROM QUESTIONS q
Hopping , It will solve your problem..
Regards
Amit Gupta (M.C.P.)
-
Yes, you are right, but I prefer to use NULLIF. It provides better performance.
ISNULL(SUM(correct)*100 /NULLIF(SUM(answered ), 0), 0)
And if you need not integer percentage you need to use
ISNULL(SUM(correct)*100.0 /NULLIF(SUM(answered ), 0), 0)
_____________
Code for TallyGenerator -
Thanks guys... perfect
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply