Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EFJj789439 for ; Fri, 14 Sep 2001 11:19:45 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8EFIKq50640 for pgsql-sql@postgresql.org; Fri, 14 Sep 2001 11:18:20 -0400 (EDT) (envelope-from news) From: joe.celko@trilogy.com (--CELKO--) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: group by weirdness Date: 14 Sep 2001 08:18:18 -0700 Organization: http://groups.google.com/ Lines: 9 Message-ID: <7e67a7b3.0109140718.483315b9@posting.google.com> References: <3B9D72B6.5050600@selectacast.net> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200109/104 X-Sequence-Number: 4768 Would this give you what you want? SELECT j.id, j.created, COUNT(mj.mid), SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1, SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2 FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id;