On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: > I have 2 tables T1 and T2 > > T1 has the columns: D, S, C. The combination of D,S,C is unique. > T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is > not unique. > > I need to produce the following result for every occurrence of T1: > D,S,C, COUNT > > COUNT is the number of matching D,S,C combinations in T2 where X = true. > There might be no matching pair in T2 or there might be match but X > is false. > > How can I express this?
Maybe something like one of these barely tested queries? select d, s, c, sum(case when t2.x then 1 else 0 end) from t1 left outer join t2 using(d,s,c) group by d, s, c; or select d,s,c, (select count(*) from t2 where t2.d=t1.d and t2.s=t1.s and t2.c=t1.c and t2.x) from t1; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
