Thread: Query

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
6                1

any suggestions?

Thanks in advance


With regards
Ashish

---

Unlimited freedom, unlimited storage. Get it now

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