Re: Problems using count() with a join - trying to format it better

Result can be obtained by:

SELECT g1.g_name,
(select count(*) from users u1 where g1.g_id = u1.g_id) as users_count,
(select count(*) from users u2 where g1.g_id = u2.g_id and u_act = 1) as
Active_users_count
FROM groups g1

Regards,


Igor


"Igor Kryltsov" <kryltsov@yahoo.com> wrote in message
news:cfrqra$1m4s$1@news.hub.org...
> Hi,
>
>
> I am using slightly modified example posted by Doug Younger and answered
by
> Tom Lane :)
> (http://archives.postgresql.org/pgsql-sql/1999-08/msg00159.php)
>
> I have the following 2 tables:
>
> Table groups:
>   g_id     int4
>   g_name   text
>
> Table users:
>   u_id     int4
>   g_id     int4  u_act    int4  0 - value means "Inactive" and 1 - value
> means "Active" (used instead of boolean type for DB interoperability :) )
> What I want is to get a count of users in each group with count of active
> users in each group, even if there are no users in the group.
>
>
> This example gives a count of users in each group:
> SELECT t1.g_name,count(t2.g_id) as users_count
>   FROM groups t1,users t2
>   WHERE t1.g_id = t2.g_id
>   GROUP BY t1.g_name;
>
> If you can help to modify it to output --> g_name, users_count,
> active_users_count
> So it could be:
> Group_A    |    89    |    34
> Group_B    |    75    |    75
> Group_C    |    25    |    0  <-- all users are inactive here
> Group_D    |    0     |    0 <---- Assume that this is a result of UNION
> which will add groups without employees
>
>
>
> Thank you,
>
> Igor
>
>