Home > mailing lists

JOIN - Mailing list pgsql-novice

From	Loredana Curugiu
Subject	JOIN
Date	June 5, 2007 13:18:38
Msg-id	1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com Whole thread Raw
Responses	Re: JOIN
List	pgsql-novice

Tree view

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

pgsql-novice by date:

From: "Loredana Curugiu"
Date: 05 June 2007, 13:15:03
Subject: Re: [SQL] JOIN

From: "Oliveiros Cristina"
Date: 05 June 2007, 13:26:59
Subject: Re: [SQL] JOIN

JOIN - Mailing list pgsql-novice

Previous

Next