And , still, in your query, you are grouping by A.dates... is there any reason
for this that I am missing ?
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;
If the dates column works as a "discriminator" to see if the row should be
considered or not,
maybe this would work
SELECT SUM(count), theme,receiver,date
FROM my_table
WHERE date=ANY(dates)
GROUP BY theme,receiver,date ;
But I don't know, do you need to include the column "dates" on output ?
Best,
Oliveiros
From: Loredana Curugiu
To: Oliveiros Cristina ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:46 PM
Subject: Re: [SQL] JOIN
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
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}
1 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | 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}
2 | LIA | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY()
returns true if B.date is on the vector A.dates, is this correct??
Correct.
Regards,
Loredana
