Way to sum items where score is one, grouped by a parent element
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
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