From: Bruno Wolff III <bruno@wolff.to>
To: Interzone <lists@interzone.gr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimal query suggestion needed
Date: Thu, 17 Jun 2004 12:54:22 -0500
Message-ID: <20040617175422.GA7171@wolff.to> (raw)
In-Reply-To: <40D1AEB0.1010604@interzone.gr>
References: <40D1AEB0.1010604@interzone.gr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, lists@interzone.gr
Subject: Re: Optimal query suggestion needed
In-Reply-To: <20040617175422.GA7171@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox