A better way of writing this query

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)

A better way of writing this query

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2007-06-08 : 11:55:22

Hi,

I have a table with 3 columns:

TestID, QuestionNo, Correct
1 1 1
1 2 1
1 3 0

Correct = 1 means that the answer is correct. Now, I need to retreive the data like this:

Pass TestID = 1
And here is the result I require.

QuestionNo Correct
3 2

This means total questions are 3 and two are correct. I don't care about the wrong answers since I am only looking for correct answers.

I created a query through which I first out the totalquestions and put them in the variables and then select the variable along with the correct answers. I am just wondering what is the best way to perform this query.

Mohammad Azam
www.azamsharp.net

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-08 : 12:04:10

[code]select count(questionno), (select count(*) from YourTable where testid = T1.TestID and correct = 1) as Numcorrect
from YourTable t1
group by testid
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-06-08 : 12:14:31

select count(questionno) as QuestionNo, sum(correct) as Correct from TABLE group by testid

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 00:45:34

quote:
Originally posted by dinakar

select count(questionno), (select count(*) from YourTable where testid = T1.TestID and correct = 1) as Numcorrect
from YourTable t1
group by testid

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

This can be simplified to


select 
      count(questionno), 
      sum(case when correct = 1 then 1 else 0 end) as Numcorrect 
from 
      YourTable t1
group by 
      testid

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 00:50:59

If correct column always have either 0 or 1 then readysetstops's method is very simple

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources