Problems using count() with a join

[Igor Kryltsov]

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 interoperabilit y :) )
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_co unt 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

[Igor Kryltsov]

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...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 interoperabilit y :) )
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_co unt
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

[Igor Kryltsov]

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_co unt
FROM groups g1

Regards,


Igor


"Igor Kryltsov" <kryltsov@yahoo .com> wrote in message
news:cfrqra$1m4 s$1@news.hub.or g...[color=blue]
> Hi,
>
>
> I am using slightly modified example posted by Doug Younger and answered[/color]
by[color=blue]
> 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 interoperabilit y :) )
> 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_co unt
> 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
>
>[/color]

[Ulrich Wisser]

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

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
[color=blue]
> 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_co unt
> FROM groups g1
>
> Regards,
>
>
> Igor
>
>
> "Igor Kryltsov" <kryltsov@yahoo .com> wrote in message
> news:cfrqra$1m4 s$1@news.hub.or g...
>[color=green]
>>Hi,
>>
>>
>>I am using slightly modified example posted by Doug Younger and answered[/color]
>
> by
>[color=green]
>>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 interoperabilit y :) )
>>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
>>
>>[/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]



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

http://archives.postgresql.org