How do I get the number of rows that have duplicate columns?

Question

let's say that the following is my table

id | name | topic | reply
...
1 | user1 | 00001 | yes              ## replied on topic 00001
2 | user2 | 00002 | yes              ** replied on topic 00002
3 | user1 | 00001 | yes              ## replied on topic 00001
4 | user5 | 00001 | no
5 | user1 | 00001 | yes              ## replied on topic 00001
6 | user1 | 00002 | no
7 | user2 | 00002 | yes              ** replied on topic 00002
8 | user3 | 00001 | no
9 | user4 | 00002 | yes

Imagine that this is a topic table. Only user1 and user reply on a single topic more than once (topic 00001 and 00002) How can I select the number of user that reply on a topic more than once, which should return 2 from 5

Answer 1

This will give you the number of users that replied more than once on a topic:

SELECT COUNT(DISTINCT name)
FROM (
    SELECT name
    FROM  yourtable
    WHERE reply = 'yes'
    GROUP BY name, topic
    HAVING COUNT(*) > 1
) T1

Answer 2

First you need to work out the number of replies per user per topic and then count up the number of distinct users that have more than one reply for any topic.

SELECT COUNT(DISTINCT name) FROM (
    SELECT name, topic, count(*) replies
    FROM table
    WHERE reply = 'yes'
    GROUP BY name, topic
) a
WHERE replies > 1