Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8HJDgd30462 for ; Mon, 17 Sep 2001 15:13:42 -0400 (EDT) (envelope-from news@news.tht.net) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail1.hub.org (8.11.3/8.11.4) with ESMTP id f8HIXRF06882 for ; Mon, 17 Sep 2001 14:33:27 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8HI7sx71356 for pgsql-sql@postgresql.org; Mon, 17 Sep 2001 14:07:54 -0400 (EDT) (envelope-from news) From: Carl van Tast X-Newsgroups: comp.databases.postgresql.sql Subject: Re: group by weirdness Date: Mon, 17 Sep 2001 20:07:39 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 39 Message-ID: <9decqt0vottpt0ju1i8re2mudhhhph24lr@4ax.com> References: <3B9D72B6.5050600@selectacast.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Newsreader: Forte Agent 1.8/32.548 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/135 X-Sequence-Number: 4807 On Sat, 15 Sep 2001 00:26:01 +0200, I wrote: > [...] >CREATE VIEW mj1 (jid, cnt) AS >SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; This should be COUNT(mid) AS cnt ... > [...] >I did not test this with PostgreSQL, but you get the idea. Well, now I did test with PostgreSQL (thanks, Jason Tishler, for your Cygwin PostgreSQL README!). PG does not support column aliases without "AS". >Probably PG is even smart enough to handle it all in one: Sure it is. So, Joseph, your solution is: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) AS cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id = ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; HTH, Carl van Tast