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

[ahaahaaha]

Hi Michael,

It is difficult to say something without seeing the data itself and its structure.

Regards,

Andrey

[michael_andrews]

Sure thing, check out this data set. Specifically, the exams with the Rev15_FormI

ExamId	ItemName	ItemScore	ExamForm
4513751	801SIM20	2	Rev15_FormF
4513751	801SIM1	2	Rev15_FormF
4513751	I120701	0	Rev15_FormF
4513751	I131584	0	Rev15_FormF
4513764	I120702	0	Rev15_FormG
4513764	801SIM20	1	Rev15_FormG
4513764	I120887	0	Rev15_FormG
4513764	801SIM1	0	Rev15_FormG
4513729	801SIM1	0	Rev15_FormI
4513729	801SIM26	0	Rev15_FormI
4513729	I131584	0	Rev15_FormI
4513729	801SIM6	0	Rev15_FormI
4513749	I131020.1	1	Rev15_FormI
4513749	I117020.1	1	Rev15_FormI
4513749	I131782.1	1	Rev15_FormI
4513749	I114476.2	1	Rev15_FormI
4514633	I114446.1	1	Rev15_FormI
4514633	I120760.2	1	Rev15_FormI
4514633	I114150.1	0	Rev15_FormI
4514633	I131980.1	0	Rev15_FormI

As you can see, based on the id, there are three different exams with the Rev15_FormI form. While each item name is different, they all have unique items per exam. If I were to just do a count of unique item names where the form is Rev15_FormI, then I would get 12, because there are 3 exams, each with 4 unique item names. However, I only care about the count for one exam in that form. I.e. if I were to do a count where exam id is 4513729 and the Form is Rev15_FormI, I would get 4, and that is the count I want to display for ALL exams with that form. See this image to point out what I'm trying to get at. http://i.imgur.com/a0Z5Kr1.png

[sunny_talwar · Accepted solution]

Is it possible that one of the ExamId had 3 exam while other had 4? How would you handle such a situation? If that can never happen, you can try this:

Only(Aggr(Count(ItemName), ExamId, ExamForm))

or

Max(Aggr(Count(ItemName), ExamId, ExamForm))

[michael_andrews]

Given our data set, that will never happen, but I appreciate the thought. I will try these out. Thanks!

[michael_andrews]

So I think you have me on the right track. However, I need to add some constraints to this count. I tried using a set expression, but it just gives me nothing when I add it. Here's what I have

MAX(Aggr(Count({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>} ItemName), ExamID, ExamForm))

Basically, what I need to say is only count ones where ItemScoreWeight > 0 AND ExamLanguageID = ENU

[sunny_talwar]

Try this:

MAX({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>}Aggr(Count({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>} ItemName), ExamID, ExamForm))