6 Replies Latest reply: Jan 26, 2017 12:02 PM by Sunny Talwar RSS

    Count Distinct If value Matches current item

    Michael Andrews

      So here is my scenario. We have exam data. Each exam record has a form foreign key. We also have exam question data, where each row is basically one question, and it is tied to the exam via a foreign key to the exam id. What I need to do, is count unique questions based on the item name, however, I only need to do it once per exam form.

       

      What I'm getting at is you may have 5 different exam records all using the same form, and I just need to count the unique question records for that exam, for one form. The sql that handles this would be

       

      SELECT COUNT(DISTINCT item_name)

        FROM exam

        INNER JOIN exam_item ON item_exam_id = exam_id

        GROUP BY exam_form

       

      The problem I'm having is it is counting unique item names for all the exams within that form, and it's giving me too big of a number. The reason is, is because that even though they share the same form, sometimes, the questions have different names. However, it does not matter because as long as we count just one exam record, we get the proper number of questions, as they always have the same number of questions, even if they're different names.

       

      How would I go about getting the unique item names for just one exam record per form? This is what the data output looks like in our view. I've tried Count(distinct ItemName) but that's giving me the wrong count. I think I may need to use a set expression? If I am, I'm just not quite clear on how to tell it, I need you to count all the unique questions (ItemName) in just the first exam record you find, that is using this current form (Rev15_FormE, Rev15_FormD) etc.

       

      See my image for a better descritpion

      items.png