Re: JOIN and aggregate problem - Mailing list pgsql-sql

From Bob Henkel
Subject Re: JOIN and aggregate problem
Date
Msg-id fedea56b0902201114q693fafabq83495c72df4a2cbf@mail.gmail.com
Whole thread Raw
In response to Re: JOIN and aggregate problem  (Bob Henkel <bob.henkel@gmail.com>)
List pgsql-sql
Scratch this one won't work for you.

On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel <bob.henkel@gmail.com> wrote:
> I might be missing something but does this solve your issue?
>
> 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);
>
> 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);
>
> SELECT t1.d, t1.s, t1.c, t2.x, COUNT(*)
> FROM t1
> INNER JOIN t2
>        ON t1.d = t2.d
>       AND t1.s = t2.s
>       AND t1.c = t2.c
>       WHERE t2.x = 'FALSE'
> GROUP BY t1.d, t1.s, t1.c, t2.x;
>
> On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz
> <postgresql@numerixtechnology.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>


pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: JOIN and aggregate problem
Next
From: Stephan Szabo
Date:
Subject: Re: JOIN and aggregate problem