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
>
>
>
>