From: Oliveiros Cristina <oliveiros.cristina@gmail.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
Date: Tue, 5 Jun 2007 06:19:06 -0700
Message-ID: <f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com> (raw)
In-Reply-To: <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@gmail.com, loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox