Skip site navigation (1) Skip section navigation (2)

Re: Optimal query suggestion needed

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Interzone <lists(at)interzone(dot)gr>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Optimal query suggestion needed
Date: 2004-06-17 17:54:22
Message-ID: 20040617175422.GA7171@wolff.to (view raw or flat)
Thread:
Lists: pgsql-sql
On Thu, Jun 17, 2004 at 14:46:08 +0000,
  Interzone <lists(at)interzone(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Jie LiangDate: 2004-06-17 18:18:56
Subject: Re: Prepare Statement
Previous:From: InterzoneDate: 2004-06-17 14:46:08
Subject: Optimal query suggestion needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group