Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA26574 for ; Thu, 19 Aug 1999 09:17:02 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA07786; Thu, 19 Aug 1999 09:16:30 -0400 (EDT) To: Doug Younger cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Problems using count() with a join In-reply-to: Your message of Wed, 18 Aug 1999 22:10:35 -0400 <4.2.0.58.19990818215523.00c808b0@proxy> Date: Thu, 19 Aug 1999 09:16:30 -0400 Message-ID: <7784.935068590@sss.pgh.pa.us> From: Tom Lane Doug Younger 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