Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 701B09FB866
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 08:59:40 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 08940-07 for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 08:59:36 -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.251])
	by postgresql.org (Postfix) with ESMTP id 534609FC8F7
	for <pgsql-novice@postgresql.org>; Tue,  5 Jun 2007 08:26:14 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so387116ana
	for <pgsql-novice@postgresql.org>; Tue, 05 Jun 2007 04:26: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:in-reply-to:mime-version:content-type:references;
	b=iTmex348nr7CmzLTBLVKi60FNuJVtLfQt+VRcnPN1jzfHg8UD0XhrtfcpyMy+csVlyaNn1yOHiilodPg6xqHDxeT8bKw6TOelPdiJ1ubpfCPiIgr9aoo8N8+uQC++4syazGUFqPy/V0mjO5kpoUWb7g927uc7GVcarRD4C5kLPM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=o+3orbDdyKW9DW6xVZkumrBRoYsAgj0Dp/i/6nnqIJhCVg545Ojk/ZUVS4yun5qp+KA9r00yDy6bkW+oSu43LoI0h02giJqorUwrAYNW82e1HRfZ/4PW0YFENgmeZZSAgxIDVa3k2dVH0dN/wHj1JTNjbcKU/1o27L5Ngpo1I0Q=
Received: by 10.100.205.9 with SMTP id c9mr3162246ang.1181042773125;
	Tue, 05 Jun 2007 04:26:13 -0700 (PDT)
Received: by 10.100.42.1 with HTTP; Tue, 5 Jun 2007 04:26:13 -0700 (PDT)
Message-ID: <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
Date: Tue, 5 Jun 2007 14:26:13 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: JOIN
In-Reply-To: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_9667_24805775.1181042773067"
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/25
X-Sequence-Number: 18923

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

Any help, please?

On 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.com> 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.
>
>
> Best,
>      Loredana
>
>
>
>

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

Any help, please?<br><br><div><span class="gmail_quote">On 6/5/07, <b class="gmail_sendername">Loredana Curugiu</b> &lt;<a href="mailto:loredana.curugiu@gmail.com">loredana.curugiu@gmail.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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><span class="sg">
&nbsp;&nbsp;&nbsp;&nbsp; Loredana<br>
<br>
<br>
<br>
</span></blockquote></div><br>

------=_Part_9667_24805775.1181042773067--