Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 353819FC138
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 10:09:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 80858-02-8 for
	<pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 10:09:37 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.240])
	by postgresql.org (Postfix) with ESMTP id C9D749FB2E5
	for <pgsql-novice@postgresql.org>; Tue,  5 Jun 2007 05:11:14 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so378860ana
	for <pgsql-novice@postgresql.org>; Tue, 05 Jun 2007 01:11:13 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	b=FxwMoSVEs8t/plqBX8NmJmPJuhHq4a8E1p9om6bs6PLhr6L/snnJ/G21K22n0RazS3IHOcYGSQoJlrJoBuJXBJzQgj8JQSW2ckYYMWdX7M2bJ3GpOqgv8QLK9tOe4dtpb/psUWqEAy/nIz4to/ivcqPU3lM7NCGKqQDF+LDLHS4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=h3kfUoPtF1OkO9c3OAx1XjkUeL8TkxmwFPHKbNhhPitIh+gvg00W7xyG/slKN12wdWyUVI8f/Pd6RYc/pnr6RvZIFsnUqMw0+6EYpEjjEuxVVSijf+JGGcvKTEyyZPa0Hmt+33SD9tJpjUy43QKemeHK7PihYXrwWJbBFi1iYQk=
Received: by 10.100.199.12 with SMTP id w12mr3062312anf.1181031072962;
	Tue, 05 Jun 2007 01:11:12 -0700 (PDT)
Received: by 10.100.42.1 with HTTP; Tue, 5 Jun 2007 01:11:12 -0700 (PDT)
Message-ID: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
Date: Tue, 5 Jun 2007 11:11:12 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: JOIN
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_7745_15336403.1181031072914"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/29
X-Sequence-Number: 18927

------=_Part_7745_15336403.1181031072914
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.


Best,
     Loredana

------=_Part_7745_15336403.1181031072914
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_7745_15336403.1181031072914--