only count users of groups 1, not twice

[ohdang888]

Quick question: should be simple, but its escaping me:

 

I want to get a count of user_ids (not the user_ids themselves, just the number) of users in 2 groups. However, if a user in both groups, they only count for 1 (because you don't want to send them 2 emails). How do i get that number:

 

This query is returning the total count, regardless if a user is in 2 groups.

 

SELECT DISTINCT user_id, COUNT(user_id) AS total FROM group_members WHERE group_id IN (2,17)

 

any thoughts?

 

thanks!

[trq]

SELECT DISTINCT user_id, COUNT(user_id) AS total FROM group_members WHERE group_id IN (2,17) GROUP BY user_id

[ohdang888]

SELECT DISTINCT user_id, COUNT(user_id) AS total FROM group_members WHERE group_id IN (2,17) GROUP BY user_id

 

That's returning a row for each user_id with the total number of groups they are in like this:

 

user id    total

42            1

53            2

65            1

 

So, theoretically i could get the total number of unique users by mysql_num_rows, but i'd like to just get that done in the query than wasting extra resources

 

any ideas?

 

thanks

[mikosiko]

no clear what exactly are you looking for as  a result... if you are looking just for the total number of users in both groups (without duplicates) this will do it

SELECT COUNT(DISTINCT user_id) as total FROM group_members WHERE group_id IN (2,17);

 

or are you looking for the list of user_id's  (without duplicates) and not matter to which group they belong?... in such case maybe this will work

(SELECT user_id FROM group_members WHERE group_id IN (2,17)
GROUP BY user_id
HAVING COUNT(user_id) = 1)
UNION
(SELECT user_id FROM group_members WHERE group_id IN (2,17)
GROUP BY user_id
HAVING COUNT(user_id) > 1)

[fenway]

I've covered this before -- the GROUP BY / HAVING solution is incredibly slow on large tables.

 

Just CROSS JOIN, once for each condition.