Skip site navigation (1) Skip section navigation (2)

Re: [SQL] JOIN

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>,<oliveiros(dot)cristina(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: [SQL] JOIN
Date: 2007-06-05 14:52:49
Message-ID: 010001c7a781$300481d0$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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(dot)cristina(at)gmail(dot)com ; pgsql-novice(at)postgresql(dot)org ; pgsql-sql(at)postgresql(dot)org 
  Sent: Tuesday, June 05, 2007 3:15 PM
  Subject: Re: [SQL] JOIN





  On 6/5/07, Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com> wrote:



    On 6/5/07, Oliveiros Cristina < oliveiros(dot)cristina(at)gmail(dot)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



In response to

Responses

pgsql-novice by date

Next:From: Loredana CurugiuDate: 2007-06-05 14:57:13
Subject: Re: [SQL] JOIN
Previous:From: Loredana CurugiuDate: 2007-06-05 14:46:43
Subject: Re: [SQL] JOIN

pgsql-sql by date

Next:From: Loredana CurugiuDate: 2007-06-05 14:57:13
Subject: Re: [SQL] JOIN
Previous:From: Loredana CurugiuDate: 2007-06-05 14:46:43
Subject: Re: [SQL] JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group