Re: JOIN

Поиск

Список

Период

Сортировка

От	Oliveiros Cristina
Тема	Re: JOIN
Дата	5 июня 2007 г. 13:26:59
Msg-id	f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com обсуждение исходный текст
Ответ на	Re: JOIN ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
Ответы	Re: JOIN ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
Список	pgsql-sql

Дерево обсуждения

Hey, Loredana.

Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?

Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)

What would be your intended sum?
3 ? 6 ?
date2 is not on dates column for that record, but it is on the first...

Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?

Thanx in advance

Best,
Oliveiros

2007/6/5, Loredana Curugiu <loredana.curugiu@gmail.com>:

Any help, please?

On 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.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

--
O Quê? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem!

В списке pgsql-sql по дате отправления:

Предыдущее

От: Ranieri Mazili
Дата: 05 июня 2007 г., 13:21:06
Сообщение: CREATE RULE with WHERE clause

Следующее

От: Richard Huxton
Дата: 05 июня 2007 г., 13:28:50
Сообщение: Re: JOIN

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Re: JOIN

Предыдущее

Следующее