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

Re: JOIN

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: JOIN
Date: 2007-06-05 11:26:13
Message-ID: 1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
Any help, please?

On 6/5/07, Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com> wrote:
>
> Hi everybody,
>
> I have the following table:
>
> 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}
> |
>
> I want to add up the count column grouped by theme and receiver for the
> dates included in the dates column.
> So  I have the following query:
>
>        SELECT SUM(A.count),
>                      A.theme,
>                      A.receiver,
>                      A.dates
>           FROM my_table A
> INNER JOIN my_table B
>               ON A.theme=B.theme
>             AND A.receiver=B.receiver
>             AND A.date=ANY(B.dates)
>  GROUP BY A.theme,A.receiver, A.dates;
>
> The result of the query is:
>
> sum | theme   |    receiver        |
> dates
>
> -------+-----------+--------------+--------------------------------------------------------------------------------
>      3 | CRIS   | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
>      2 | CRIS   | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>      3 | CRIS   | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>    18 | CRIS   | +40741775622 |
> {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,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
>      4 | LIA      | +40741775621 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
>      6 | LIA      | +40741775621 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>    10 | LIA      | +40741775621 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> The result is wrong. I don't know what it is wrong at my query.
> Please help.
>
>
> Best,
>      Loredana
>
>
>
>

In response to

  • JOIN at 2007-06-05 08:11:12 from Loredana Curugiu

Responses

  • Re: JOIN at 2007-06-05 12:13:46 from Richard Huxton
  • Re: [SQL] JOIN at 2007-06-05 13:19:06 from Oliveiros Cristina

pgsql-novice by date

Next:From: Richard HuxtonDate: 2007-06-05 12:13:46
Subject: Re: JOIN
Previous:From: Loredana CurugiuDate: 2007-06-05 08:11:12
Subject: JOIN

pgsql-sql by date

Next:From: Richard HuxtonDate: 2007-06-05 12:13:46
Subject: Re: JOIN
Previous:From: Charles.HouDate: 2007-06-05 10:33:09
Subject: the right time to vacuum database?

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