Re: Optimal query suggestion needed

From: InterZone <lists(at)interzone(dot)gr>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Optimal query suggestion needed
Date: 2004-06-17 19:22:34
Message-ID: 40D1EF7A.1040302@interzone.gr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Bruno Wolff III wrote:
> 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

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.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruno Wolff III 2004-06-17 20:01:23 Re: Optimal query suggestion needed
Previous Message InterZone 2004-06-17 19:06:09 Re: Optimal query suggestion needed