From: | Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: JOIN and aggregate problem |
Date: | 2009-02-23 15:00:59 |
Message-ID: | 20090223150059.56fa8c3f@dick.coachhouse |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, 20 Feb 2009 11:15:09 -0800 (PST)
Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> 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.
> >
>
> 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;
this works
> 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;
this works too
From a performance point of view, is one preferable to the other?
Many thanks for your help!
--
Tarlika Elisabeth Schmitz
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2009-02-23 15:44:05 | Re: JOIN and aggregate problem |
Previous Message | Tarlika Elisabeth Schmitz | 2009-02-23 14:17:12 | Re: JOIN and aggregate problem |