Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9B0C09FBD2E for ; Tue, 5 Jun 2007 10:26: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 40938-03-2 for ; Tue, 5 Jun 2007 10:26:48 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.191]) by postgresql.org (Postfix) with ESMTP id 32A1D9FC677 for ; Tue, 5 Jun 2007 10:19:08 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so2204866mue for ; Tue, 05 Jun 2007 06:19:06 -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=c+cFPgSfNQmxQdiMNdlNZELgJre3lx05zPz0Rwhk6j0B0Ab/yk+eURUfEckNKL7Tj9b8FYLyrsmliHqjPKccB1UgxzDHsd/Q5VzTq9mM+rQvwNYMLomq2OFCQJU3MgcCF4uFpKIxBPvAeAezS115pf+i8bj6DJyX1hpC9CO+7B4= 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=HZ74q4AnSE5l24VdbvYBjpfZQSOb7v1fX8xCp7hZjJ4lvbcjK0Tae/+Iq0O0Rnuta2GYN4IaYyQaSSXjb+twJuJlCEdGJmLmlJDAG4Qk0Fy43wG6EbQrfBlb32XnukFYQ80KuE/ddLA4OVw2Fr8df25TT5tVrD0F+3CNyuW/kC8= Received: by 10.82.186.5 with SMTP id j5mr8746544buf.1181049546319; Tue, 05 Jun 2007 06:19:06 -0700 (PDT) Received: by 10.82.135.7 with HTTP; Tue, 5 Jun 2007 06:19:06 -0700 (PDT) Message-ID: Date: Tue, 5 Jun 2007 06:19:06 -0700 From: "Oliveiros Cristina" To: "Loredana Curugiu" Subject: Re: [SQL] JOIN Cc: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org In-Reply-To: <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7666_28591947.1181049546268" References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/30 X-Sequence-Number: 18928 ------=_Part_7666_28591947.1181049546268 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hey, Loredana. Please advice me, you need to sum for a certain pair (Theme, receiver) the number that appear= s on count for every record whose date is in dates column, is this correct? 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 ? 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 ? Thanx in advance Best, Oliveiros 2007/6/5, Loredana Curugiu : > > Any help, please? > > On 6/5/07, Loredana Curugiu 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=3DB.theme > > AND A.receiver=3DB.receiver > > AND A.date=3DANY(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 > > > > > > > > > --=20 O Qu=EA? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem! ------=_Part_7666_28591947.1181049546268 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hey, Loredana.

Please advice me,
you need to sum for a certain pa= ir (Theme, receiver) the number that appears on count for every record whos= e date is in dates column, is this correct?
But in what record's dat= es column? On all of them? Or just the dates column of the records that hav= e 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 ?
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 ou= tput for ex for ,
CRIS   | +40741775622 ?
And For
LIA = | +40741775621 ?

Thanx in advance

Best,
Oliveiros

2007/6/5, Loredana Curugiu <loredana.curugiu@gmail.com>:
Any help, please?=

On 6/5/07, Loredana Curugiu <lore= dana.curugiu@gmail.com> wrote:
Hi everybody,

I have the following table:

count | theme  |   receiver        |             date                  |            &n= bsp;            = ;            dates           &nbs= p;                                                 &nb= sp;            =    | -------+-----------+----------------------+--------------------------------= ----+----------------------------------------------------------------------= ---------------------------------------------------+-------------------
      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} |

I want to add up the count colum= n 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
            &nb= sp; ON A.theme=3DB.theme
            AND A.re= ceiver=3DB.receiver
            AND A.date=3DANY(B.dates)
 GROUP BY A.theme,A.receiver, A.dates;

The result of the query is:

sum | theme   |    receiver        |            &n= bsp;            = ;            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







--
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_7666_28591947.1181049546268--