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 8A84DD1B18E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jun 2004 16:19:03 -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 71084-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jun 2004 19:18:56 +0000 (GMT)
Received: from mail.interzone.gr (server.interzone.gr [193.41.229.197])
	by svr1.postgresql.org (Postfix) with ESMTP id 94741D1B175
	for <pgsql-sql@postgresql.org>; Thu, 17 Jun 2004 16:18:54 -0300 (ADT)
Received: by mail.interzone.gr (Postfix, from userid 1005)
	id 58FCC236FF; Thu, 17 Jun 2004 22:18:53 +0300 (EEST)
Received: from interzone.gr (ppp3-adsl-44.ath.forthnet.gr [212.251.110.44])
	by mail.interzone.gr (Postfix) with ESMTP
	id 4E768236F9; Thu, 17 Jun 2004 22:18:51 +0300 (EEST)
Message-ID: <40D1EF7A.1040302@interzone.gr>
Date: Thu, 17 Jun 2004 22:22:34 +0300
From: InterZone <lists@interzone.gr>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7b) Gecko/20040421
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimal query suggestion needed
References: <40D1AEB0.1010604@interzone.gr> <20040617175422.GA7171@wolff.to>
In-Reply-To: <20040617175422.GA7171@wolff.to>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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/152
X-Sequence-Number: 18097



Bruno Wolff III wrote:
> On Thu, Jun 17, 2004 at 14:46:08 +0000,
>   Interzone <lists@interzone.gr> wrote:
> 
>>I want to create a view that will have:
>>from table t0 the elements "code", "address" and "mun"
>>from table t1 the elements "code" and "pname"
>>from table t2 the total number of elements, and the total number of 
>>elements where avail = true, for every value t0_fk (foreign key to t0) 
>>and t1_fk (foreigh key to t1).
>>
>>After several attempts and changes as the requirements changed, I finaly 
>>came up with that :
>>
>>select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname 
>>count(t2.code) as t2total, (select count(t2.code) as t2avail from t2 
>>where t2.avail = true and t2.t0_fk=t0.code and t2.t1_fk = t1.code) 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
> 
> 
> This approach is actually pretty close. I think you just didn't pick a
> good way to count the avail = true rows.
> I think you can replace the above with:
> 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) 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

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.

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.