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

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: JOIN
Date: 2007-06-05 08:11:12
Message-ID: 1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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

Responses

  • Re: JOIN at 2007-06-05 11:26:13 from Loredana Curugiu
  • Re: JOIN at 2007-06-05 13:42:27 from Sean Davis

pgsql-novice by date

Next:From: Loredana CurugiuDate: 2007-06-05 11:26:13
Subject: Re: JOIN
Previous:From: Ashish KaralkarDate: 2007-06-05 06:04:53
Subject: Assigning Password to New created user

pgsql-sql by date

Next:From: Charles.HouDate: 2007-06-05 10:33:09
Subject: the right time to vacuum database?
Previous:From: Tino WildenhainDate: 2007-06-05 07:51:59
Subject: Re: Encrypted column

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