Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B9C699FBC9E; Tue, 5 Jun 2007 11:52:57 -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 68522-03; Tue, 5 Jun 2007 11:52:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by postgresql.org (Postfix) with ESMTP id 595FF9FB4E4; Tue, 5 Jun 2007 11:52:52 -0300 (ADT) Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id LBXD4C24; Tue, 5 Jun 2007 15:52:49 +0100 Message-ID: <010001c7a781$300481d0$ec5a3d0a@marktestcr.marktest.pt> From: "Oliveiros Cristina" To: "Loredana Curugiu" , , , References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com> <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> Subject: Re: [SQL] JOIN Date: Tue, 5 Jun 2007 15:52:49 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00FD_01C7A789.91A05330" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.232 tagged_above=0 required=5 tests=AWL=0.095, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/36 X-Sequence-Number: 18934 This is a multi-part message in MIME format. ------=_NextPart_000_00FD_01C7A789.91A05330 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello again, Loredana. Additional information required :-) imagine the following situation 1| LIA | recv1 | date1 | (date1,date2) 2|LIA | recv1 |date 1 | (date2,date3) 3| LIA | recv1 | date1 | (date1,date3) Should this yield 6? Or 4? date 1 is not on the second dates column, but it is on the remaining two Cheers, Oliveiros ----- Original Message -----=20 From: Loredana Curugiu=20 To: oliveiros.cristina@gmail.com ; pgsql-novice@postgresql.org ; = pgsql-sql@postgresql.org=20 Sent: Tuesday, June 05, 2007 3:15 PM Subject: Re: [SQL] JOIN 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 = this correct? Yap. =20 But in what record's dates column? On all of them? Or just the = dates column of the records that have that (Theme , Receiver) ?=20 Suppose I have 3| CRIS | rec1 | date1 | (date1,date2) 3| CRIS | rec1 | date2 | (date1,date3) What would be your intended sum? 3 ? 6 ?=20 3=20 date2 is not on dates column for that record, but it is on the = first...=20 Could you please show me an example of what would be the correct = output for ex for , CRIS | +40741775622 ? And For=20 LIA | +40741775621 ? Let's take a look at the following data: count | theme | receiver | date = | dates=20 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} = | =20 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} = | =20 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-0= 6-07} | =20 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-0= 6-10} | =20 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} = | =20 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-0= 6-10} | =20 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-0= 6-09} | =20 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-0= 6-10} | =20 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} = | =20 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} = | =20 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-0= 6-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 =20 SELECT SUM(B.count),=20 A.theme,=20 A.receiver,=20 A.dates=20 FROM view_sent_messages A=20 INNER JOIN view_sent_messages B=20 ON A.theme=3DB.theme=20 AND A.receiver=3DB.receiver=20 AND B.date=3DANY (A.dates)=20 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-0= 6-07} 5 | CRIS | +40741775622 | = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-0= 6-08} 4 | CRIS | +40741775622 | = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-0= 6-09} 9 | CRIS | +40741775622 | = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-0= 6-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 sum shoud be 3. =20 The same problem it is at the eigth row. The sum should be 2. Best regards, Loredana ------=_NextPart_000_00FD_01C7A789.91A05330 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello again, Loredana.
 
 
Additional information required = :-)
 
imagine the following = situation
 
1| LIA | recv1 | date1 | = (date1,date2)
2|LIA | recv1 |date 1 | = (date2,date3)
3| LIA | recv1 | date1 | = (date1,date3)
 
 
Should this yield 6? Or 4?
date 1 is not on the second dates = column, but it is=20 on the remaining two
 
Cheers,
Oliveiros
----- Original Message -----
From:=20 Loredana Curugiu
Sent: Tuesday, June 05, 2007 = 3:15=20 PM
Subject: Re: [SQL] JOIN



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


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

Hi Oliveiros!=20 Nice to "see" you again!

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

Yap. 

But=20 in what record's dates column? On all of them? Or just the dates = column of=20 the records that have that (Theme , Receiver) ?

Suppose I=20 have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | = date2 |=20 (date1,date3)

What would be your intended sum?
3 ? 6 ?=20

3

date2=20 is not on dates column for that record, but it is on the first...=20

Could you please show me an example of what would be the = correct=20 output for ex for ,
CRIS   | +40741775622 ?
And = For=20
LIA | +40741775621 ?

Let's take a look at the following data:

count=20 | theme  |   receiver       =20 |            =20 date             =20     |        dates =
      2 | LIA      | = +40741775621 |=20 2007-06-02 00:00:00+00 |=20 = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} =         =20        =20 = |            =     =20
      1 | LIA      | = +40741775621 |=20 2007-06-04 00:00:00+00 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} =             &= nbsp;   =20 = |            =     =20
      3 | CRIS   | +40741775622 | = 2007-06-01=20 00:00:00+00 |=20 = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-0= 6-07}=20 = |            =     =20
      1 | CRIS   | +40741775622 | = 2007-06-04=20 00:00:00+00 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-0= 6-10}=20 = |            =     =20
      2 | LIA      |=20 +40741775621 | 2007-06-03 00:00:00+00 |=20 = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} =          =20       =20 = |            =     =20
      1 | CRIS   | +40741775622 | = 2007-06-04=20 00:00:00+00 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-0= 6-10}=20 = |            =     =20
      1 | CRIS   | +40741775622 | = 2007-06-03=20 00:00:00+00 |=20 = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-0= 6-09}=20 = |            =     =20
      1 | CRIS   | +40741775622 | = 2007-06-04=20 00:00:00+00 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-0= 6-10}=20 = |            =     =20
      4 | = LIA      |=20 +40741775621 | 2007-06-01 00:00:00+00 |=20 = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} =             &= nbsp;   =20 = |            =     =20
      1 | = LIA      |=20 +40741775621 | 2007-06-04 00:00:00+00 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} =             &= nbsp;   =20 = |            =     =20
      1 | CRIS   | +40741775622 |=20 2007-06-02 00:00:00+00 |=20 = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-0= 6-08}=20 |


We can see that for LIA if we sum the = count per=20 day we have the=20 = following:
theme         = =20          =20 = date           &nb= sp;           =20             =20 = count
LIA           = 2007-06-01=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 4
LIA           = 2007-06-02=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 2
LIA           = 2007-06-03=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 2
LIA           = 2007-06-04=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 2

Also for=20 = CRIS:

theme          =          =20 = date           &nb= sp;           =20              count
CRIS=20           2007-06-01=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 3
CRIS           = 2007-06-02=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 1
CRIS           = 2007-06-03=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 1
CRIS           = 2007-06-04=20 = 00:00:00+00          &n= bsp;           &nb= sp;=20 3


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

I obtain = the=20 following result:

 sum | theme  |   = receiver=20      =20 = |            =             &= nbsp;           =20 dates
  =20 = -----+----------+---------------------+----------------------------------= ----------------------------------------------
      8 | CRIS | +40741775622 |=20 = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-0= 6-07}
 =20     5 | CRIS | +40741775622 |=20 = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-0= 6-08}
 =20     4 | CRIS | +40741775622 |=20 = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-0= 6-09}
      9 = |=20 CRIS | +40741775622 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-0= 6-10}
   =20 10 | LIA    | +40741775621 |=20 = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
&n= bsp;=20     6 | LIA    | +40741775621 |=20 = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
&n= bsp;    =20 4 | LIA    | +40741775621 |=20 = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
&n= bsp;=20     4 |=20 LIA    | +40741775621 |=20 = {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We=20 can see that for example for the first row, the sum 8 it is = correctly=20 calculated because if we sum the count for the days from dates = column.
If=20 we take a look at the fourth row we can see that the sum it is not = correct:=20 it should be taken values for the count only for the date=20 2007-06-04
The sum shoud be 3.
 

The same problem it is at the eigth row. The sum should be=20 2.

Best regards,
        =20 = Loredana


= ------=_NextPart_000_00FD_01C7A789.91A05330--