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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
