Jump to content

Retrieving unique information


bljepp69

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?

 

 

 

Link to comment
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--]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.