From: | Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: JOIN and aggregate problem |
Date: | 2009-02-23 14:17:12 |
Message-ID: | 20090223141712.5d25dee1@dick.coachhouse |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, 20 Feb 2009 19:06:48 +0000
Richard Huxton <dev(at)archonet(dot)com> wrote:
> 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.
>
> try something like:
>
> SELECT t1.d, t1.s, t1.c, count(*)
> FROM t1
> LEFT JOIN (
> SELECT d,s,c FROM t2 WHERE x
> ) AS t2_true USING (d,s,c)
> GROUP BY t1.d, t1.s, t1.c;
>
> Warning - not tested
Many thanks for the quick reply.
This suggestion does not work as it returns a count of 1 even when
there are no rows in t2 that match (d,s,c) in T1.
--
Best Regards,
Tarlika Elisabeth Schmitz
A: Because it breaks the logical sequence of discussion
Q: Why is top posting bad?
From | Date | Subject | |
---|---|---|---|
Next Message | Tarlika Elisabeth Schmitz | 2009-02-23 15:00:59 | Re: JOIN and aggregate problem |
Previous Message | Tarlika Elisabeth Schmitz | 2009-02-21 16:00:14 | Re: JOIN and aggregate problem |