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
