Percent data from two tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Percent data from two tables

Author

Topic

stevehayter
Starting Member

1 Post

Posted - 2006-10-16 : 14:45:34

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-16 : 15:07:55

You can just duplicate them and slap a division sign between them, that does work, but you're right in thinking it is not efficient because SQL Server will run the extra subqueries again even though they are the same. What you can do is create a view based on the query you already have and then query the view to create the percent column - the result is the same, but the subqueries only run once each.

If your answered and correct columns are always 1 or 0 as shown in your sample data then this might is probably better. The isnull calls may be unecessary, depends on whether or not answered or correct can be null.

SELECT q.id, q.text,
sum(isnull(qh.answered, 0)) as Attempted,
sum(isnull(qh.correct, 0)) as Correct,
sum(isnull(qh.correct, 0)) / sum(isnull(qh.answered, 0)) as Percentage
FROM Questions q
INNER JOIN QuestionHistory qh ON qh.assessment_question_id = q.ID
GROUP BY q.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources