Retrieving unique information

[bljepp69]

Here is a simplified version of a db table I'm using:

 

| index | survey_id  | quest_id | sess_id |
+-------+------------+----------+---------+
|    1  |      10    |      1   |  aaa   |
|    2  |      10    |      2   |  aaa   |
|    3  |      10    |      3   |  aaa   |
|    4  |      33    |      1   |  aaa   |
|    5  |      33    |      2   |  aaa   |
|    6  |      10    |      1   |  bbb   |
|    7  |      10    |      2   |  bbb   |
|    8  |      10    |      3   |  bbb   |
|    9  |      44    |      1   |  ccc   |
|   10  |      55    |      1   |  aaa   |
|   11  |      55    |      2   |  aaa   |
|   12  |      44    |      1   |  ddd   |
|   13  |      44    |      1   |  aaa   |
+-------+------------+----------+---------+

So what I want to retrieve is the number of times the survey_id occurs, but only count it once per sess_id. So, in the example above, survey_id 10 would be have a count of 2, 33 would be 1, 44 would be 3, 55 would be 1.

 

Note that the quest_id's are tied to the survey_id's and don't really mean anything in this particular query. The sess_id column is there specifically for this query.

 

Basically, I'm tracking user answers to a survey, and I have a function to show the most active surveys for a given time period. So, the interpretation of the above data shows that survey 10 has three questions, and was answered by two unique users. Therefore, I only want to count it twice, not 6 times.

 

Lastly, in any given session a user can answer multiple surveys. That's why you see the sess_id show up multiple times.

 

I'm pretty sure this can be one with one query, but I can't seem to come up with the correct syntax. I know it can be done with two queries, but I'm trying to avoid that. Anyone have any suggestions for the correct query syntax?

 

 

 

[bljepp69]

I think I got it. Any comments on this?

 

SELECT survey_id FROM thistable GROUP BY survey_id,sess_id

[leokwan1982]

How about this? (MySQL 4.1 or Later)

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] s.survey_id, ([span style=\'color:blue;font-weight:bold\']select[/span] count(DISTINCT sess_id) FROM thistable t where t.survey_id = s.survey_id) as 'occurs'

FROM thistable s GROUP BY survey_id

[!--sql2--][/div][!--sql3--]