Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9E2809FA618 for ; Tue, 5 Jun 2007 11:30:50 -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 91529-02 for ; Tue, 5 Jun 2007 11:30:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 10CBE9FB644 for ; Tue, 5 Jun 2007 11:30:43 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so203779uge for ; Tue, 05 Jun 2007 07:30:42 -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:cc:in-reply-to:mime-version:content-type:references; b=Aw7GaTFJh7Y72paRrAC6Z4jEVAEXeRb3X0qDh9Jo9xiHV7v6BGTepToySU9hzeywh/N8OsCj5H2U7KOPG+S80CTNipDf1HYbFt6GEKJcUdm3zAQKzl+kMRDSjjTEV+FOaQYB+psZIqlpk3Mq7HqenjUMcZx7/whwf9weztkxASk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=VKHHTOvH3qVHnwXD089E1H4jFwo5TtmpBUkctGkZ25XVg8am8we0Y3cFKPy9Bv0DtNNN7v0Y2YZLf8bksStBUJ9hHL51s6fwKSesSBcoan0zj5AE672NhuQT1SpC1S5VY/9AaFNPSn6DDz2hVHLvXQVER1NUds8ZqDiW9ljPwJQ= Received: by 10.82.126.5 with SMTP id y5mr8919625buc.1181053841559; Tue, 05 Jun 2007 07:30:41 -0700 (PDT) Received: by 10.82.135.7 with HTTP; Tue, 5 Jun 2007 07:30:41 -0700 (PDT) Message-ID: Date: Tue, 5 Jun 2007 15:30:41 +0100 From: "Oliveiros Cristina" To: "Loredana Curugiu" Subject: Re: [SQL] JOIN Cc: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org In-Reply-To: <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9264_24927274.1181053841498" References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com> <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.032 tagged_above=0 required=5 tests=AWL=0.030, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/34 X-Sequence-Number: 18932 ------=_Part_9264_24927274.1181053841498 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hey, Loredana. Nice to "see" you too ;-) Thank you for your detailed clarifications. Hmm...try to add the following clause to your INNER JOIN AND A.date =3D B.Date Like this : INNER JOIN view_sent_messages B ON A.theme=3DB.theme AND A.receiver=3DB.receiver AND A.date =3D b.Date AND B.date=3DANY (A.dates) I have not your data here, so I am not sure if it'll work. Also, Ive never worked with vectors on Postgres. I am assuming ANY() return= s true if B.date is on the vector A.dates, is this correct?? Loredane, Then please let me hear bout the result Best, Oliveiros 2007/6/5, Loredana Curugiu : > > > > On 6/5/07, Loredana Curugiu wrote: > > > > > > > > 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 t= his > > > 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=3DB.theme > > AND A.receiver=3DB.receiver > > AND B.date=3DANY (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 correctl= y > > 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 sum shoud be 3. > > > The same problem it is at the eigth row. The sum should be 2. > > > > Best regards, > > Loredana > > > > > --=20 O Qu=EA? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem! ------=_Part_9264_24927274.1181053841498 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hey, Loredana.
Nice to "see" you too ;-)

Thank you for = your detailed clarifications.

Hmm...try to add the following clause = to your INNER JOIN
AND A.date =3D B.Date

Like this :

INNER JOIN view_sent_messages B
            &nb= sp; ON A.theme=3DB.theme
            AND A.re= ceiver=3DB.receiver
         &n= bsp;   AND A.date =3D b.Date
             AND B.da= te=3DANY (A.dates)

I have not your data here, so I am not sure if i= t'll work.
Also, Ive never worked with vectors on Postgres. I am as= suming ANY() returns true if B.date is on the vector=20 A.dates, is this correct??

Loredane, Then please let me hear bout th= e result

Best,
Oliveiros

2007/6/5, Loredana Curugiu = <loredana.curugiu@gmail.co= m>:


On 6/5/07, Loredana Curugiu < loredana.curugiu@gmail.com> wrote:


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. 

B= ut in what record's dates column? On all of them? Or just the dates col= umn 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 i= s 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 ou= tput 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} &n= bsp;                |            &n= bsp;   
      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} &n= bsp;            = ;    |            &n= bsp;   
      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} |            &n= bsp;   
      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} |            &n= bsp;   
      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} &n= bsp;                |            &n= bsp;   
      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} |            &n= bsp;   
      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} |            &n= bsp;   
      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} |            &n= bsp;   
      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} &n= bsp;            = ;    |            &n= bsp;   
      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} &n= bsp;            = ;    |            &n= bsp;   
      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:<= br> theme                    date            = ;                         count
LIA           2007-06-01 00:00:00+00          &nbs= p;             4
LIA           2007-06-02 00:00:00+00          &nbs= p;             2
LIA           2007-06-03 00:00:00+00          &nbs= p;             2
LIA           2007-06-04 00:00:00+00          &nbs= p;             2

Also for CRIS:

theme                    date            = ;                         count
CRIS           2007-06-01 00:00:00+00          &nbs= p;             3
CRIS           2007-06-02 00:00:00+00          &nbs= p;             1
CRIS           2007-06-03 00:00:00+00          &nbs= p;             1
CRIS           2007-06-04 00:00:00+00          &nbs= p;             3


With the following query 
     SELECT SUM(B.count),
               =     A.theme,
            &nb= sp;      A.receiver,
                = ;   A.dates
         FROM view_sent_messages A =
INNER JOIN view_sent_messages B
            &nb= sp; ON A.theme=3DB.theme
            AND A.re= ceiver=3DB.receiver
            AND B.da= te=3DANY (A.dates)
  GROUP BY A.theme,A.receiver, A.dates;

 I obtain the following result:

 sum | theme  |   receiver       |            &n= bsp;            = ;            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,2= 007-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&= nbsp;   | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-0= 6-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 sum shoud be 3.  

<= div>
The same problem it is at the eigth row. The sum should be 2.

Best regards,
         Loredana





--
O Qu=EA? SQL Server 200= 5 Express Edition? for free?  easy-to-use??  lightweigh= t???  and embeddable???  Isso deve ser uma fortuna, hom= em! ------=_Part_9264_24927274.1181053841498--