From: Oliveiros Cristina <oliveiros.cristina@gmail.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
Date: Tue, 5 Jun 2007 15:30:41 +0100
Message-ID: <f54607780706050730i23979220t97bdef2e199c09d6@mail.gmail.com> (raw)
In-Reply-To: <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com>
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
<f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
<1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
<1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com>
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 = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (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() returns
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 <loredana.curugiu@gmail.com>:
>
>
>
> 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.
> >
> > 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 sum shoud be 3.
>
>
> The same problem it is at the eigth row. The sum should be 2.
> >
> > Best regards,
> > Loredana
> >
> >
>
--
O Quê? SQL Server 2005 Express Edition? for
free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma
fortuna, homem!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@gmail.com, loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <f54607780706050730i23979220t97bdef2e199c09d6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox