469,658 Members | 1,872 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

Problems using count() with a join

Hi,I am using slightly modified example posted by Doug Younger and answered
by Tom Lane
:)(http://archives.postgresql.org/pgsql...8/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 |
34Group_B | 75 | 75Group_C | 25 | 0 <-- all users
are inactive hereGroup_D | 0 | 0 <---- Assume that this is a
result of UNION which will add groups without employeesThank you,Igor
Nov 23 '05 #1
3 2370
Hi,
I am using slightly modified example posted by Doug Younger and answered by
Tom Lane :)
(http://archives.postgresql.org/pgsql...8/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
Nov 23 '05 #2
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" <kr******@yahoo.com> wrote in message
news:cf***********@news.hub.org...
Hi,
I am using slightly modified example posted by Doug Younger and answered by Tom Lane :)
(http://archives.postgresql.org/pgsql...8/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

Nov 23 '05 #3
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" <kr******@yahoo.com> wrote in message
news:cf***********@news.hub.org...
Hi,
I am using slightly modified example posted by Doug Younger and answered


by
Tom Lane :)
(http://archives.postgresql.org/pgsql...8/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 ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Mr. Mountain | last post: by
reply views Thread by Michael Manuel via .NET 247 | last post: by
28 posts views Thread by Jay | last post: by
1 post views Thread by EnjoyNews | last post: by
22 posts views Thread by MP | last post: by
1 post views Thread by alexwm20 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.