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:15:34
Message-ID: 1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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: Oliveiros CristinaDate: 2007-06-05 14:30:41
Subject: Re: [SQL] JOIN
Previous:From: Loredana CurugiuDate: 2007-06-05 14:08:44
Subject: Re: [SQL] JOIN

pgsql-sql by date

Next:From: Brian MathisDate: 2007-06-05 14:26:08
Subject: Re: Encrypted column
Previous:From: Marko KreenDate: 2007-06-05 14:12:11
Subject: Re: Encrypted column

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