Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 6BEBE9FB671;
	Tue,  5 Jun 2007 10:44:24 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 44820-01-3; Tue,  5 Jun 2007 10:44:13 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from nihrelayxway2.hub.nih.gov (mailfwd.nih.gov [128.231.90.107])
	by postgresql.org (Postfix) with ESMTP id 7E77F9FC91B;
	Tue,  5 Jun 2007 10:42:29 -0300 (ADT)
X-IronPortListener: NIH_Relay
X-SBRS: None
X-IronPort-AV: E=Sophos;i="4.16,385,1175486400"; d="scan'208";a="301005208"
Received: from pressa.nci.nih.gov (HELO [137.187.209.230]) ([137.187.209.230])
	by nihrelayxway2.hub.nih.gov with ESMTP; 05 Jun 2007 09:42:27 -0400
Message-ID: <46656843.6000504@mail.nih.gov>
Date: Tue, 05 Jun 2007 09:42:27 -0400
From: Sean Davis <sdavis2@mail.nih.gov>
User-Agent: Thunderbird 1.5.0.8 (X11/20060911)
MIME-Version: 1.0
To: Loredana Curugiu <loredana.curugiu@gmail.com>
CC: pgsql-novice@postgresql.org,  pgsql-sql@postgresql.org
Subject: Re: JOIN
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
In-Reply-To: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/31
X-Sequence-Number: 18929

Loredana Curugiu wrote:
> Hi everybody,
> 
> I have the following table:
> 
> count | theme  |   receiver        |             date                 
> |                                     dates                             
>                                                |
> -------+-----------+----------------------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-------------------
> 
>       2 | LIA      | +40741775621 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}         
>         |                
>       1 | LIA      | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}                 
> |                
>       3 | CRIS   | +40741775622 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |                
>       1 | CRIS   | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |                
>       2 | LIA      | +40741775621 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}          
>        |                
>       1 | CRIS   | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |                
>       1 | CRIS   | +40741775622 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |                
>       1 | CRIS   | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |                
>       4 | LIA      | +40741775621 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}                 
> |                
>       1 | LIA      | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}                 
> |                
>       1 | CRIS   | +40741775622 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> |
> 
> I want to add up the count column grouped by theme and receiver for the
> dates included in the dates column.
> So  I have the following query:
> 
>        SELECT SUM(A.count),
>                      A.theme,
>                      A.receiver,
>                      A.dates
>           FROM my_table A
> INNER JOIN my_table B
>               ON A.theme=B.theme
>             AND A.receiver=B.receiver
>             AND A.date=ANY(B.dates)
>  GROUP BY A.theme,A.receiver, A.dates;
> 
> The result of the query is:
> 
> sum | theme   |    receiver        |                                    
> dates
> -------+-----------+--------------+--------------------------------------------------------------------------------
>      3 | CRIS   | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
>      2 | CRIS   | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>      3 | CRIS   | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>    18 | CRIS   | +40741775622 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
>      4 | LIA      | +40741775621 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
>      4 | LIA      | +40741775621 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
>      6 | LIA      | +40741775621 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>    10 | LIA      | +40741775621 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> 
> The result is wrong. I don't know what it is wrong at my query.
> Please help.

Loredana,

It is great to see your determination to get the answer, but we still do
not know what is "wrong" with the query result.  You will need to
explain what you think is wrong before anyone can help.  The output
looks like it matches the query perfectly.

Sean