Received: from wizard.ctel.net (root@wizard.ctel.net [209.222.96.43]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA55249 for ; Fri, 27 Aug 1999 04:07:09 -0400 (EDT) (envelope-from kain@dynup.net) Received: from avalon.com (kain@enh1-p47.modems.tdi.net [204.157.64.47]) by wizard.ctel.net (8.9.3/8.9.3) with ESMTP id EAA14608 for ; Fri, 27 Aug 1999 04:02:28 -0400 Message-ID: X-Mailer: XFMail 1.3 [p0] on Linux X-Priority: 3 (Normal) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit MIME-Version: 1.0 Date: Fri, 27 Aug 1999 04:15:12 -0400 (EDT) Sender: kain@avalon.com From: "Scott A. Barron" To: pgsql-sql@postgresql.org Subject: Showing rows where COUNT() is 0 Hello all, Given the following tables: T1 T2 T3 ------------------------------------------- usid | c sid | usid | c sid | usid | r where uid, sid, and r are int's, and c is text I have the query: SELECT t1.c, COUNT(t2.usid), AVG(t3.r) FROM t1, t2, t3 WHERE t2.usid = t1.usid AND t3.sid = t2.sid GROUP BY t1.c Which gives me the total number of sid's in t2 belonging to t1.usid and the average of t3.r for each t2.sid belonging to each t1.usid. The query works fine exept it doesn't show output where COUNT() returns 0. I would like to list all of the results even if there is nothing in t2 to match the usid in t1. Is there a way I can make this happen? Thanks, Scott