pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 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 13:16 Tom Lane <tgl@sss.pgh.pa.us> parent: Doug Younger <postgres@mindspring.com> 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