sql queries using group by

Question

I have a quiz_tracking table (in mysql) that contains the ff fields:

subjectid - the id of the subject ( think of your subjects in school)
assessmentname - the name of the assessment ( think of it as exam or like chapter exams, the exams you found at the end of each chapter) found in each subject
questionid - the id of the question
userid - the id of the user who took the exam
attempt - attempt number
answer - answer given
score - score gained for this question

rownum - disregard this column, i only put in this one so its easier to point out which row numbers i am particularly interested in.

When the user takes a quiz, the records are recorded here. The assessments have different number of questions. For this example, first assessment has 3, second assessment has 3, third assessment has 1. The user can leave the quiz at the middle, in this case the answer column will be null since the quiz taker abandoned it.

I created a sql fiddle for the table and the data.

http://sqlfiddle.com/#!9/a41473/1

Basically, what I want to come up is I need to filter this data set and present a data set that only contains completed assessment attempts. Meaning, if the assessment has 3 questions and all these questions were answered (answer is not null) then that should be in the filtered data set.

The way I figured out how to determine if there was a complete assessments is via this sql:

select max(a.question_count) from ( 
select count(distinct qt.questionid) as 'question_count'
      from quiz_tracking qt
      where qt.subjectid=22380
      and qt.assessmentname = 'first assessment'
      and qt.userid in (555,121)
      group by qt.attempt, qt.userid ) a )

I count all the question ids. Then i do a

having ( sum(if(answer is not null,1,0)) = result of above subquery.

The assumptions here are, the subjectid is provided, all the assessment names are provided all the userids are provided.

In the sql fiddle,i can do it for 1 assessment (e.g. 'first assessment'), but what I need to do is to produce a filtered data set that contains all the assessments (first assessment, second assessment, third assessment). The expected result should be row numbers 1,2,3,4,5,6,12,13,14,15,16,17,21,22.

Parfait · Accepted Answer · 2017-10-27 02:56:18Z

1

Simply join the main table to an aggregate derived table that calculates question count and answer count, then in outer query return when both are equal:

select z.*, q_cnt.question_count, q_cnt.answer_count
from  quiz_tracking z

inner join
  (select c.userid, c.subjectid, c.assessmentname, c.attempt,
          count(distinct c.questionid) as 'question_count',
          sum(if(c.answer is not null,1,0)) as 'answer_count'
   from quiz_tracking c
   group by c.userid, c.subjectid, c.assessmentname, c.attempt) q_cnt

on q_cnt.userid = z.userid
and q_cnt.subjectid = z.subjectid
and q_cnt.assessmentname = z.assessmentname
and q_cnt.attempt = z.attempt

where  q_cnt.question_count = q_cnt.answer_count

SQL Fiddle DEMO

edited Oct 27, 2017 at 2:56

answered Oct 27, 2017 at 2:50

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

1 Comment

bencampbell_14 Over a year ago

Thanks! This was the key... q_cnt.question_count = q_cnt.answer_count

Collectives™ on Stack Overflow

sql queries using group by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related