Re: JOIN

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: JOIN
Date: 2007-06-05 13:42:27
Message-ID: 46656843.6000504@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-sql

Loredana Curugiu 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.

Loredana,

It is great to see your determination to get the answer, but we still do
not know what is "wrong" with the query result. You will need to
explain what you think is wrong before anyone can help. The output
looks like it matches the query perfectly.

Sean

In response to

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

Browse pgsql-novice by date

  From Date Subject
Next Message Loredana Curugiu 2007-06-05 14:08:44 Re: [SQL] JOIN
Previous Message Richard Huxton 2007-06-05 13:21:44 Re: JOIN

Browse pgsql-sql by date

  From Date Subject
Next Message Alvaro Herrera 2007-06-05 13:59:51 Re: Encrypted column
Previous Message Brian Mathis 2007-06-05 13:31:02 Re: Encrypted column