From: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
To: oliveiros.cristina@gmail.com
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
Date: Tue, 5 Jun 2007 15:52:49 +0100
Message-ID: <010001c7a781$300481d0$ec5a3d0a@marktestcr.marktest.pt> (raw)
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
<f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
<1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
<1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com>
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 -----
From: Loredana Curugiu
To: oliveiros.cristina@gmail.com ; pgsql-novice@postgresql.org ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:15 PM
Subject: Re: [SQL] JOIN
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
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@marktest.pt, loredana.curugiu@gmail.com, oliveiros.cristina@gmail.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <010001c7a781$300481d0$ec5a3d0a@marktestcr.marktest.pt>
* 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