Calculating count of a certain values in columns with different tables

Question

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:

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?

SqlZim · Accepted Answer · 2017-10-04 18:46:12Z

2

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

edited Oct 4, 2017 at 18:46

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

answered Oct 4, 2017 at 18:31

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TyForHelpDude Over a year ago

Aaand can you helpme find ratio of these true and false count for each question. I added this for another column but now work; ", (100* TrueCount/FalseCount) as ratio"

Juan Carlos Oropeza Over a year ago

dont take negative votes to seriously. You have to live with them, just accept it and move on.

TyForHelpDude Over a year ago

Yeah thanks Juan I am here because there is more like you here than like them.

Juan Carlos Oropeza Over a year ago

You cant use the alias, you have to write the formula again. Check edit for example.

SqlZim Over a year ago

You can use 100 * avg(case when UserAnswer = CorrectAnswer then 1.0 else 0.0 end) instead of dealing with the division and count(*)

|

SqlZim · Accepted Answer · 2017-10-04 18:26:22Z

1

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;

answered Oct 4, 2017 at 18:26

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

3 Comments

Juan Carlos Oropeza Over a year ago

You forgot the aggregation for question.

TyForHelpDude Over a year ago

@JuanCarlosOropeza I think you are right can you provide details, thanks

SqlZim Over a year ago

@JuanCarlosOropeza Ack, that's what I get for getting called away.

Collectives™ on Stack Overflow

Calculating count of a certain values in columns with different tables

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related