Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id E95F19FBC47
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 11:08:52 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 16277-01-3 for
	<pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  5 Jun 2007 11:08:40 -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 97C769FB9A9
	for <pgsql-novice@postgresql.org>; Tue,  5 Jun 2007 11:08:45 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so396623ana
	for <pgsql-novice@postgresql.org>; Tue, 05 Jun 2007 07:08:44 -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=bUJjgWO7MyHOkHpdj/7wnN1asoU5EUamlt8K06zJYtS8F8lHu+F/iJcBqW4jUK9H2v7ZIPZ/7fJvt52j6QmoSEomzpWJLpZ6PSy/6qxJG2IgoNlosTvLeuzclzVXsrShEX1z0VMhuxlbrB0smhuF9HdUDiKGSZNW9e304l1WcZQ=
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=sutAcqJwfjfeMHzv8rXz17xjSjJ3db/V43Z8/Hztix5ICIqtQ8zb2Aiqc2SRX+6Xdvhti7uVVJz4cXXUx09U7STCHD9jeLFteGxLdAKaRviDQc3om7ZPgv7LtSlaEWosmI7Af8cI70KXpElzjScHxwhan4Qcvdl808Owrd6ZuIw=
Received: by 10.100.119.14 with SMTP id r14mr3277196anc.1181052524128;
	Tue, 05 Jun 2007 07:08:44 -0700 (PDT)
Received: by 10.100.42.1 with HTTP; Tue, 5 Jun 2007 07:08:44 -0700 (PDT)
Message-ID: <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
Date: Tue, 5 Jun 2007 17:08:44 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: oliveiros.cristina@gmail.com, pgsql-novice@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_12543_18570798.1181052524083"
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
	<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
	<f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/32
X-Sequence-Number: 18930

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

On 6/5/07, Oliveiros Cristina <oliveiros.cristina@gmail.com> wrote:
>
> Hey, Loredana.


Hi Oliveiros! Nice to "see" you again!

Please advice me,
> you need to sum for a certain pair (Theme, receiver) the number that
> appears on count for every record whose date is in dates column, is this
> correct?


Yap.

But in what record's dates column? On all of them? Or just the dates column
> of the records that have that (Theme , Receiver) ?
>
> Suppose I have
> 3| CRIS | rec1 | date1 | (date1,date2)
> 3| CRIS | rec1 | date2 | (date1,date3)
>
> What would be your intended sum?
> 3 ? 6 ?


3

date2 is not on dates column for that record, but it is on the first...
>
> Could you please show me an example of what would be the correct output
> for ex for ,
> CRIS   | +40741775622 ?
> And For
> LIA | +40741775621 ?


Let's take a look at the following data:

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}
|


We can see that for LIA if we sum the count per day we have the following:
theme                    date                                     count
LIA           2007-06-01 00:00:00+00                        4
LIA           2007-06-02 00:00:00+00                        2
LIA           2007-06-03 00:00:00+00                        2
LIA           2007-06-04 00:00:00+00                        2

Also for CRIS:

theme                    date                                     count
CRIS           2007-06-01 00:00:00+00                        3
CRIS           2007-06-02 00:00:00+00                        1
CRIS           2007-06-03 00:00:00+00                        1
CRIS           2007-06-04 00:00:00+00                        3


With the following query
     SELECT SUM(B.count),
                   A.theme,
                   A.receiver,
                   A.dates
         FROM view_sent_messages A
INNER JOIN view_sent_messages B
              ON A.theme=B.theme
            AND A.receiver=B.receiver
            AND B.date=ANY (A.dates)
  GROUP BY A.theme,A.receiver, A.dates;

I obtain the following result:

 sum | theme  |   receiver       |                                     dates

-----+----------+---------------------+--------------------------------------------------------------------------------
      8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
      5 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
      4 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
      9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
    10 | LIA    | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
      6 | LIA    | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
      4 | LIA    | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
      4 | LIA    | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}

We can see that for example for the first row, the sum 8 it is correctly
calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not
correct: it should be taken values for the count only for the date
2007-06-04.
The same problem it is at the eigth row.

Best regards,
         Loredana

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

<br><br><div><span class="gmail_quote">On 6/5/07, <b class="gmail_sendername">Oliveiros Cristina</b> &lt;<a href="mailto:oliveiros.cristina@gmail.com">oliveiros.cristina@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;">
Hey, Loredana.</blockquote><div><br>
Hi  <span class="gmail_quote"><span class="gmail_sendername">Oliveiros! Nice to &quot;see&quot; you again!<br>
</span></span></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Please advice me,<br>you need to sum for a
certain pair (Theme, receiver) the number that appears on count for
every record whose date is in dates column, is this correct?</blockquote><div><br>
Yap.&nbsp; <br>
</div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">But in what record&#39;s dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
<br><br>Suppose I have<br>3| CRIS | rec1 | date1 | (date1,date2)<br>3| CRIS | rec1 | date2 | (date1,date3)<br><br>What would be your intended sum?<br>3 ? 6 ? </blockquote><div><br>
3 <br>
</div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">date2 is not on dates column for that record, but it is on the first...
<br><br>Could you please show me an example of what would be the correct output for ex for ,<br>CRIS&nbsp;&nbsp; | +40741775622 ?<br>And For <br>LIA  |
+40741775621 ?</blockquote><div><br>
Let&#39;s take a look at the following data:<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; dates
<div id="mb_0">&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></div>
<br>
<br>
We can see that for LIA if we sum the count per day we have the following:<br>
theme&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; count<br>
LIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2007-06-01
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4<br>
LIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2007-06-02
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
LIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2007-06-03
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
LIA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2007-06-04
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
<br>
Also for CRIS:<br>
<br>
theme&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; count<br>
CRIS &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2007-06-01
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
3<br>
CRIS&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2007-06-02
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1<br>
CRIS &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2007-06-03
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1<br>
CRIS &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2007-06-04
00:00:00+00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
3<br>

<br>
<br>
With the following query&nbsp; <br>
&nbsp; &nbsp;&nbsp; SELECT SUM(B.count), <br>
&nbsp; &nbsp; &nbsp; &nbsp;&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;&nbsp;&nbsp;&nbsp; A.receiver, <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; A.dates <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM view_sent_messages A <br>
INNER JOIN view_sent_messages 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;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND B.date=ANY (A.dates) <br>
&nbsp; GROUP BY A.theme,A.receiver, A.dates;<br>
<br>
I obtain the following result:<br>
<br>
&nbsp;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;
dates<br>
&nbsp;&nbsp;
-----+----------+---------------------+--------------------------------------------------------------------------------<br>
&nbsp; &nbsp; &nbsp; 8 | CRIS | +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; 5 | CRIS | +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; 4 | CRIS | +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; &nbsp;<span style="color: rgb(204, 0, 0);"> 9 </span>| CRIS | +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; 10 | LIA&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; 6 | LIA&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;&nbsp;&nbsp; 4 | LIA&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; &nbsp; <span style="color: rgb(204, 0, 0);">4</span> | LIA&nbsp;&nbsp;&nbsp; | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}<br>
<br>
We can see that for example for the first row, the sum 8 it is
correctly calculated because if we sum the count for the days from
dates column.<br>
If we take a look at the fourth row we can see that the sum it is not
correct: it should be taken values for the count only for the date
2007-06-04. <br>
The same problem it is at the eigth row.<br>
<br>
</div></div>Best regards,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Loredana<br>
<br>

------=_Part_12543_18570798.1181052524083--