Hi Igor,
wouldn't
select g_name,count(*),sum(u_act) from g1 join users using(g_id) group by g_name
do the job?
/Ulrich
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
