Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id DAAF19FBEF9; Tue, 5 Jun 2007 12:00:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 95657-02; Tue, 5 Jun 2007 12:00:30 -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 031BA9FB4C4; Tue, 5 Jun 2007 12:00:28 -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 LBXD4CJV; Tue, 5 Jun 2007 16:00:27 +0100 Message-ID: <012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt> From: "Oliveiros Cristina" To: "Loredana Curugiu" , "Oliveiros Cristina" , , References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com> <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> <1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com> Subject: Re: [SQL] JOIN Date: Tue, 5 Jun 2007 16:00:27 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0123_01C7A78A.A2911110" 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.225 tagged_above=0 required=5 tests=AWL=0.088, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/38 X-Sequence-Number: 18936 This is a multi-part message in MIME format. ------=_NextPart_000_0123_01C7A78A.A2911110 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable And , still, in your query, you are grouping by A.dates... is there any = reason for this that I am missing ? SELECT SUM(A.count), A.theme,=20 A.receiver,=20 A.dates=20 FROM my_table A=20 INNER JOIN my_table B=20 ON A.theme=3DB.theme=20 AND A.receiver=3DB.receiver AND A.date=3DANY(B.dates) GROUP BY A.theme,A.receiver, A.dates; If the dates column works as a "discriminator" to see if the row should = be considered or not,=20 maybe this would work SELECT SUM(count), theme,receiver,date FROM my_table WHERE date=3DANY(dates) GROUP BY theme,receiver,date ; But I don't know, do you need to include the column "dates" on output = ? Best,=20 Oliveiros =20 From: Loredana Curugiu=20 To: Oliveiros Cristina ; pgsql-novice@postgresql.org ; = pgsql-sql@postgresql.org=20 Sent: Tuesday, June 05, 2007 3:46 PM Subject: Re: [SQL] JOIN Hmm...try to add the following clause to your INNER JOIN=20 AND A.date =3D B.Date Like this : INNER JOIN view_sent_messages B=20 ON A.theme=3DB.theme=20 AND A.receiver=3DB.receiver=20 AND A.date =3D b.Date AND B.date=3DANY (A.dates)=20 Doesn't work. I get the result 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-0= 6-07} 1 | CRIS | +40741775622 | = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-0= 6-08} 1 | 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} 4 | LIA | +40741775621 | = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 2 | LIA | +40741775621 | = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 2 | 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}=20 Which is not correct. The wrong values ( red colored ) remain as = before adding the clause. And now it is summed the counter's values per day ( first day of dates array ). I have not your data here, so I am not sure if it'll work.=20 Also, Ive never worked with vectors on Postgres. I am assuming ANY() = returns true if B.date is on the vector A.dates, is this correct??=20 Correct. =20 Regards, Loredana ------=_NextPart_000_0123_01C7A78A.A2911110 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
And , still, in your query, you are = grouping by=20 A.dates... is there any reason for this that I am missing ?
 
     SELECT=20 SUM(A.count),
               =  =20     A.theme,
       =   =20            A.receiver,=20
        =       =20       A.dates
        =  =20 FROM my_table A
INNER JOIN my_table B=20
           &nb= sp; =20 ON A.theme=3DB.theme=20
            = AND=20 A.receiver=3DB.receiver
            AND = A.date=3DANY(B.dates)
 GROUP BY A.theme,A.receiver, = A.dates;
 
 
If the dates column works as a = "discriminator" to=20 see if the row should be considered or not,
maybe this would work
 
SELECT SUM(count),=20 theme,receiver,date
FROM my_table
WHERE = date=3DANY(dates)
GROUP BY theme,receiver,date = ;
 
  But I don't know, do you = need to=20 include the column "dates" on output ?
 
Best,
Oliveiros
 
From:=20 Loredana Curugiu
To: Oliveiros Cristina ; = pgsql-novice@postgresql.org ; pgsql-sql@postgresql.org =
Sent: Tuesday, June 05, 2007 = 3:46=20 PM
Subject: Re: [SQL] JOIN

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

Like this :

INNER JOIN = view_sent_messages=20 B
          = ;   =20 ON A.theme=3DB.theme=20 =
            = AND=20 A.receiver=3DB.receiver=20
           =  =20 AND A.date =3D b.Date
          &nb= sp;=20 AND B.date=3DANY (A.dates)

Doesn't work. I get the result

 sum | theme  = |   receiver  =20 = |            =             &= nbsp;           =20 = dates
-----+--------+--------------+----------------------------------= ----------------------------------------------
  =20 3 | 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 1 | 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 1 | 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}
  =20 9 | 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 4 | LIA    | +40741775621 |=20 = {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
&n= bsp; =20 2 | LIA    | +40741775621 |=20 = {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
&n= bsp; =20 2 | LIA    | +40741775621 |=20 = {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
&n= bsp;  = 4=20 | LIA    | +40741775621 |=20 {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} =

 Which is = not=20 correct. The wrong values (  red colored ) remain as=20 before
 adding the clause. And now it is summed  the = counter's=20 values per day
 ( first day of dates array ).

I=20 have not your data here, so I am not sure if it'll work.
Also, = Ive never=20 worked with vectors on Postgres. I am assuming ANY() returns true if = B.date=20 is on the vector A.dates, is this correct??
=
Correct.
 
Regards,
     &n= bsp; =20 Loredana
------=_NextPart_000_0123_01C7A78A.A2911110--