Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2010-05-12 : 16:05:37
|
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? |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-12 : 16:08:31
|
| Use DITINCT in COUNT like COUNT(DISTINCT Participant.PartId) |
 |
|
|
|
|
|
Getting a count of unique records.