Re: Query

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