Bruno Wolff III wrote:
On Thu, Jun 17, 2004 at 14:46:08 +0000, Interzone <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
