antic
Perch
Wondering if someone can help me with this..
I have a survey system, fairly basic, which stores Questions, Participants and their Answers:
Usually there is only 1 Answer record for each Participant-Question combination.
However, for multiple choice questions, there may be more than one Answer record - one for each value selected.
Here's an example, where question 1 is multiple choice.
In blue: Participant 1 ticks answer 3 for question 1.
In red: Participant 2 ticks answers 2 and 3 for question 1:
Given the data above, I want to COUNT the number of participants who answered either 2 or 3 to question 1.
The correct result is 2 participants. My SELECT statement might be (simplified):
SELECT COUNT(Participant.PartId) AS PartCount [...inner joins...] WHERE (QuestionId = 1) AND (Answer IN (2, 3))
However, this will of course return PartCount = 3, because 3 records match the criteria, even though 2 of those are the same participant. So my problem is, how do I tell SQL to ignore duplicate participants and give me the result of 2 unique participants matching the criteria?
I have a survey system, fairly basic, which stores Questions, Participants and their Answers:
Usually there is only 1 Answer record for each Participant-Question combination.
However, for multiple choice questions, there may be more than one Answer record - one for each value selected.
Here's an example, where question 1 is multiple choice.
In blue: Participant 1 ticks answer 3 for question 1.
In red: Participant 2 ticks answers 2 and 3 for question 1:
Given the data above, I want to COUNT the number of participants who answered either 2 or 3 to question 1.
The correct result is 2 participants. My SELECT statement might be (simplified):
SELECT COUNT(Participant.PartId) AS PartCount [...inner joins...] WHERE (QuestionId = 1) AND (Answer IN (2, 3))
However, this will of course return PartCount = 3, because 3 records match the criteria, even though 2 of those are the same participant. So my problem is, how do I tell SQL to ignore duplicate participants and give me the result of 2 unique participants matching the criteria?
