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

From: Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
To: Igor Kryltsov <kryltsov(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Problems using count() with a join - trying to format
Date: 2004-08-20 09:42:14
Message-ID: 4125C775.4020506@relevanttraffic.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)yahoo(dot)com> wrote in message
> news:cfrqra$1m4s$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
>
>>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(at)postgresql(dot)org)
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2004-08-20 09:47:04 Re: insert waiting
Previous Message John Sidney-Woollett 2004-08-20 09:40:43 Re: making two tables with identical schemas appear as