Alan Alan - 1 year ago 58
MySQL Question

MySql query: what's the better way to get the number of unique pairs in a set?

There is a member_gorup table:

id member_id group_id
-- --------- --------
1 23 17
2 23 17
3 23 13
4 24 17
5 24 5
6 25 13

What needs to be found is how many unique(distinct) member_ids each group_id has. In this case it is:

17 - 2
13 - 2
5 - 1

I managed to achieve this by using the following query:

SELECT COUNT(`group_id`), `group_id` FROM
(SELECT `member_id`, `group_id`
FROM `member_groups`
GROUP BY `member_id`, `group_id`) AS `groups`
GROUP BY `group_id`

Is there a way to achieve the same result without using the nested query - perhaps with DISTINCT?

(optional question: if so is it faster?)

Answer Source

COUNT() has an optional DISTINCT syntax for exactly this kind of problem:

SELECT `group_id`, COUNT(DISTINCT `member_id`) AS count_members 
FROM `member_groups` 
GROUP BY `group_id`;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download