Обсуждение: Re: Problems using count() with a join - trying to format it better
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
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 > >
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" <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 >> >> > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >