From: | Bob Henkel <bob(dot)henkel(at)gmail(dot)com> |
---|---|
To: | Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: JOIN and aggregate problem |
Date: | 2009-02-20 19:23:47 |
Message-ID: | fedea56b0902201123s20545b40i365eb3957a2e8159@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
How about this?
CREATE TABLE t1(d INT,s INT, c INT);
CREATE UNIQUE INDEX idx01_t1
ON t1 USING btree (d, s, c);
INSERT INTO t1 (d, s, c)
VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
CREATE TABLE t2(d INT,s INT, c INT, x boolean);
INSERT INTO t2(d, s, c, x)
VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE),(5,5,5,TRUE),(5,5,5,TRUE);
SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) END
FROM t1
LEFT OUTER JOIN t2
ON t1.d = t2.d
AND t1.s = t2.s
AND t1.c = t2.c
AND t2.x = TRUE
GROUP BY t1.d, t1.s, t1.c,t2.x;
--DROP TABLE t1;
--DROP TABLE t2;
On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz
<postgresql(at)numerixtechnology(dot)de> 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?
>
>
>
> --
>
>
> Best Regards,
>
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tarlika Elisabeth Schmitz | 2009-02-21 16:00:14 | Re: JOIN and aggregate problem |
Previous Message | Stephan Szabo | 2009-02-20 19:15:09 | Re: JOIN and aggregate problem |