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

Re: [SQL] JOIN

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: 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:08:44
Message-ID: 1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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 same problem it is at the eigth row.

Best regards,
         Loredana

In response to

Responses

  • Re: [SQL] JOIN at 2007-06-05 14:15:34 from Loredana Curugiu
  • Re: JOIN at 2007-06-06 12:46:26 from Loredana Curugiu

pgsql-novice by date

Next:From: Loredana CurugiuDate: 2007-06-05 14:15:34
Subject: Re: [SQL] JOIN
Previous:From: Sean DavisDate: 2007-06-05 13:42:27
Subject: Re: JOIN

pgsql-sql by date

Next:From: Marko KreenDate: 2007-06-05 14:12:11
Subject: Re: Encrypted column
Previous:From: Richard Broersma JrDate: 2007-06-05 14:07:29
Subject: Re: CREATE RULE with WHERE clause

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