CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TyForHelpDude
TyForHelpDude

Reputation: 5001

Calculating count of a certain values in columns with different tables

There is two table Questions and Answer. Question contains 65 questions(other means 65 rows) consist of quiestionid and correcAnswer of question. Answer table contains answers of users and userId

My manager asked me to build such a query that should tell us; for each question how many true answer and false answer. percentage of true answers over total answers(trueAnswerCount/answersCount)

There is some others, i handled them but the ones above are tough, this is how I tried so far;

select SoruId as QuestionID, userId, UserAnswer, oss.CorrectAnswer, 
case  
  When UserAnswer = CorrectAnswer Then 'TRUE'
  else 'FALSE'
end
from OnlineSinav.Cevap osc
join OnlineSinav.Soru oss on osc.SoruId=oss.ID;

this gives me the result below: enter image description here

From the result above all I need total true count and false count for each QuestionNo, userid Tells me only when new 65 rows of question belongs to new user so I don't need it but it's creepy how can I achieve this?

Upvotes: 0

Views: 31

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

following SqlZim answer you need the aggregation by QuestionNo

select 
    QuestionNo = osc.SoruId
  , TrueCount  = sum(case when UserAnswer = CorrectAnswer then 1 else 0 end)
  , FalseCount = sum(case when UserAnswer = CorrectAnswer then 0 else 1 end)
  , TruePercent  = 100.0 * avg(case when UserAnswer = CorrectAnswer then 1.0 else 0.0 end)
  , FalsePercent = 100.0 * avg(case when UserAnswer = CorrectAnswer then 0.0 else 1.0 end)
from OnlineSinav.Cevap osc
inner join OnlineSinav.Soru oss 
    on osc.SoruId=oss.ID
group by osc.SoruId

Upvotes: 2

SqlZim
SqlZim

Reputation: 38073

using conditional aggregation: 

select 
    TrueCount  = sum(case when UserAnswer = CorrectAnswer then 1 else 0 end)
  , FalseCount = sum(case when UserAnswer = CorrectAnswer then 0 else 1 end)
from OnlineSinav.Cevap osc
  inner join OnlineSinav.Soru oss 
    on osc.SoruId=oss.ID;

Upvotes: 1

Related Questions