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

On Mon, 10 Dec 2007 12:23:49 +0000 (GMT)
Ashish Karalkar <ashish_postgre@yahoo.co.in> 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

type_id count
1 2

1 3

6 1

This is not to "only one type" or I didn't get the question.

Something like

create table pippo ( user_id int, type_id int);
insert into pippo values(1,1);
insert into pippo values(1,2);
insert into pippo values(2,1);
insert into pippo values(3,3);
insert into pippo values(4,3);
insert into pippo values(5,1);
insert into pippo values(1,10);
insert into pippo values(7,6);

select type_id, count(*) from pippo group by type_id having
count(*)=1;

10;1
6;1
2;1

--
Ivan Sergio Borgonovo
http://www.webthatworks.it