Jump to content

Archived

This topic is now archived and is closed to further replies.

bljepp69

Retrieving unique information

Recommended Posts

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?

 

 

 

Share this post


Link to post
Share on other sites

I think I got it. Any comments on this?

 

SELECT survey_id FROM thistable GROUP BY survey_id,sess_id

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.