Problems using count() with a join

From: Doug Younger <postgres(at)mindspring(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problems using count() with a join
Date: 1999-08-19 02:10:35
Message-ID: 4.2.0.58.19990818215523.00c808b0@proxy
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,
I have the following 2 tables:

Table groups:
g_id int4
g_name text

Table users:
u_id int4
g_id int4

What I want is to get a count of # of users in each group, even if there
are no users in the group.

I have tried the following:
SELECT t1.g_name,count(t2.g_id)
FROM groups t1,users t2
WHERE t1.g_id = t2.g_id
GROUP BY t1.g_name;

But it only give the counts of users in each group if there actually are
users in that group.
I want the count to be 0 if there are no users in the group.

Thanks.

Browse pgsql-sql by date

  From Date Subject
Next Message tjk@tksoft.com 1999-08-19 05:20:36 Re: [SQL] Counting the number of distinct rows returned
Previous Message Drew Whittle 1999-08-18 23:59:21 Counting the number of distinct rows returned