Ashish Karalkar wrote:
Hello there,
I am having data in table something like below:
user_id type_id
1 1
1 2
2 1
3 3
4 3
5 1
1 10
7 6
What i want is the count of all user group by type_id who are subscribed to only one type e.g
Part 1: Find users with only one type_id
SELECT user_id, max(type_id) as type_id
FROM user_types
GROUP BY user_id
HAVING count(*) = 1;
You could use min(type_id) instead of course, since the HAVING clause
means there is only one type for each user-id.
Part 2: Summarise on type_id
SELECT type_id, count(*)
FROM
(
SELECT user_id, max(type_id) as type_id
FROM user_types
GROUP BY user_id
HAVING count(*) = 1
) AS users_with_one_type
GROUP BY type_id;
Note - not tested, might contain syntax errors
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
