Postgres Pro
Downloads
Home   >   mailing lists

Re: JOIN - Mailing list pgsql-sql

From Loredana Curugiu
Subject Re: JOIN
Date
Msg-id 1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com
Whole thread Raw
In response to Re: JOIN  ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
Responses Re: JOIN
List pgsql-sql
Tree view


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


pgsql-sql by date:

Previous
From: "Loredana Curugiu"
Date:
Subject: Re: JOIN
Next
From: "Oliveiros Cristina"
Date:
Subject: Re: JOIN