0 Replies Latest reply: Mar 1, 2017 9:22 AM by Michael Andrews RSS

    Way to sum items where score is one, grouped by a parent element

    Michael Andrews

      Hi, I have a pivot table that shows item responses. Item responses are basically answers to questions on an exam, so one exam can have multiple responses. I need to either create a variable, or figure out a way to get the top score per exam, for each item. The way that is calculated right now, is based on the exam catalog entry, and exam form, we count the max number of item responses for that given form and catalog item, and then use that as a max for all the responses within that form and catalog item. The sql would look something like this.

       

      SELECT TOP(100) SUM(COALESCE(CASE WHEN exam_item.item_score = 1 THEN 1 ELSE 0 END, exam_item.item_score)) AS _correct
        ,exam_excat_id
        , exam_form
        FROM exam_item_response
             INNER JOIN exam_item ON exam_item_response.resp_item_id = exam_item.item_id
             INNER JOIN exam ON exam.exam_id = exam_item.item_exam_id
        GROUP BY exam_excat_id, exam_form
      

       

      Basically then, each item that matches the exam_excat_id and exam_form, I need that max score. Which is essentially the max count of items where the item score was = 1 grouped by their excat id and form

       

      What's the best way to go about this?