CODEWITHSUNDEEP
sqlsql-server-2008
WinFXGuy
WinFXGuy

Reputation: 1599

SQL query question

Here is the table "Answers". I need to get count of QuestionNo that are "R", "W", "" for each section for given AcademicYear and TestNo. What is the best SQL query? 

AcademicYear  StudentID  TestNo Section QuestionNo  Answer

2010-2011        1        1        2        1        R
2010-2011        1        1        2        2        W
2010-2011        1        1        2        3        R
2010-2011        1        1        2        4         
2010-2011        1        1        2        5         
2010-2011        1        1        2        6         
2010-2011        1        1        2        7         
2010-2011        1        1        2        8         
2010-2011        1        1        2        9         
2010-2011        1        1        2        10

The end result should look like this:

Section QuestionNo  Answer   Count
   2         1         R       15
   2         1         W       25
   2         1                 100
   2         2         R       10
   2         2         W       50
   2         2                 10
   etc..

Sorry for not framming the question right for the first time.

Upvotes: 0

Views: 113

Answers (2)

mikerobi
mikerobi

Reputation: 20878

EDIT

I added some columns to the output to be more consistent with your question.

Just replace the ??? with the values you want.

SELECT section, question, answer, COUNT(*) AS count
FROM table 
WHERE academicYear = ??? AND testNo = ???
GROUP BY section, question, answer

Upvotes: 3

Mike
Mike

Reputation: 6050

select 
academicyear, testno,

 sum(case when answer='R' then 1 else 0 end) as Right,
 sum(case when answer='W' then 1 else 0 end) as Wrong
from 
T
group by academicyear, testno

Upvotes: 2

Related Questions