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