4

I apologize in advanced if I am not explaining this correctly. I can barely explain it in english terms, let alone in a mysql query.

I am trying to get the list of response_set_ids that have more than 1 record for a question_id.

This is an example of my data:

+----+-----------------+-------------+-----------+
| id | response_set_id | question_id | answer_id |
+----+-----------------+-------------+-----------+
|  1 |              10 |           1 |         4 |
|  2 |              10 |           2 |         5 |
|  3 |              10 |           3 |         6 |
|  4 |              10 |           3 |         7 |
|  5 |              11 |           1 |         8 |
|  6 |              11 |           2 |         9 |
|  7 |              11 |           3 |        10 |
+----+-----------------+-------------+-----------+

I would like to have a query that would return me a list response_set_ids, and in this particular example, I would expect to get returned 10 because that response_set has question_id -> 3 showing up more than once.

Please let me know if you need any further information to help me.

I have tried this: select response_set_id, count(question_id) from responses group by response_set_id;

But that only gives me the counts of questions per response_set.

Thank you in advanced!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Kashlin
  • 43
  • 5

3 Answers3

1
select distinct response_set_id from (
    select  response_set_id , question_id  
    from 
    responses
    group by 
    response_set_id, question_id
    having count(*)>1) a
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13
1

The simplest method doesn't use a subquery:

SELECT DISTINCT response_set_id
FROM responses
GROUP BY response_set_id, question_id
HAVING COUNT(*) > 1;

This is one of the very, very few instances where select distinct is used (appropriately) with group by.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I believe this question has been asked before but I cannot add comments at my current rep:

SELECT DISTINCT response_set_id
FROM responses
GROUP BY question_id
HAVING COUNT(question_id) > 1
CSSBurner
  • 1,565
  • 15
  • 13
  • 2
    Thousands of times Im sure – Daniel Marcus May 29 '18 at 19:16
  • This did not work. It returned me records that do not match what I am trying to do. And my apologies if this has been asked thousands of times, I cannot find something that works. I need to find the response_set_id which has the same question_id more than once. Question_id can be used in other response_set_ids. – Kashlin May 29 '18 at 19:19