X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4688D1B297 for ; Thu, 17 Jun 2004 16:55:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05196-04 for ; Thu, 17 Jun 2004 19:55:21 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 7D321D1B34B for ; Thu, 17 Jun 2004 16:55:18 -0300 (ADT) Received: (qmail 28782 invoked by uid 500); 17 Jun 2004 20:01:23 -0000 Date: Thu, 17 Jun 2004 15:01:23 -0500 From: Bruno Wolff III To: InterZone Cc: pgsql-sql@postgresql.org Subject: Re: Optimal query suggestion needed Message-ID: <20040617200123.GA28736@wolff.to> Mail-Followup-To: InterZone , pgsql-sql@postgresql.org References: <40D1AEB0.1010604@interzone.gr> <20040617175422.GA7171@wolff.to> <40D1EF7A.1040302@interzone.gr> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <40D1EF7A.1040302@interzone.gr> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/153 X-Sequence-Number: 18098 On Thu, Jun 17, 2004 at 22:22:34 +0300, InterZone wrote: > > Thanks > the query you sent failed on v. 7.4, so I added an "end" to the case > statement. I selected from the tables and the results seem to be correct. That was an unintentional typo. > > I rewrite it for archiving reasons: > > select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname > count(t2.code) as t2total, count(case when t2.avail then 1 else NULL > end) as t2avail from t0, t1, t2 where t2.t0_fk = t0.code and > t2.t1_fk=t1.code group by t0.code, t0.address, t0.mun, t1.code, t1.pname > > > Once again thank you. Your welcome.