From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Ashish Karalkar <ashish_postgre(at)yahoo(dot)co(dot)in> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query |
Date: | 2007-12-10 12:44:09 |
Message-ID: | 475D3499.1090602@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2007-12-10 12:59:42 | Re: Query |
Previous Message | Ashish Karalkar | 2007-12-10 12:23:49 | Query |