Jump to content


Photo

Retrieving unique information


  • Please log in to reply
2 replies to this topic

#1 bljepp69

bljepp69
  • Members
  • PipPipPip
  • Advanced Member
  • 111 posts
  • LocationSacramento, CA

Posted 16 September 2005 - 05:53 AM

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?



Guru? No, but I do RTFM

#2 bljepp69

bljepp69
  • Members
  • PipPipPip
  • Advanced Member
  • 111 posts
  • LocationSacramento, CA

Posted 16 September 2005 - 06:27 AM

I think I got it. Any comments on this?

SELECT survey_id FROM thistable GROUP BY survey_id,sess_id

Guru? No, but I do RTFM

#3 leokwan1982

leokwan1982
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationHong Kong

Posted 16 September 2005 - 06:48 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users