pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems using count() with a join
2+ messages / 2 participants
[nested] [flat]

* Problems using count() with a join
@ 1999-08-19 02:10 Doug Younger <postgres@mindspring.com>
  1999-08-19 13:16 ` Re: [SQL] Problems using count() with a join Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Doug Younger @ 1999-08-19 02:10 UTC (permalink / raw)
  To: 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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Problems using count() with a join
  1999-08-19 02:10 Problems using count() with a join Doug Younger <postgres@mindspring.com>
@ 1999-08-19 13:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-08-19 13:16 UTC (permalink / raw)
  To: Doug Younger <postgres@mindspring.com>; +Cc: pgsql-sql

Doug Younger <postgres@mindspring.com> writes:
> 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.

Try SELECT as above plus

... UNION
    SELECT t1.g_name, 0
    FROM groups t1
    WHERE NOT EXISTS(SELECT * FROM users t2 WHERE t1.g_id = t2.g_id);

Grotty, I know, but we don't have outer joins yet...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-08-19 13:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-19 02:10 Problems using count() with a join Doug Younger <postgres@mindspring.com>
1999-08-19 13:16 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox