Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id C6EF89FB46D
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Thu, 10 May 2007 05:01:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 49362-05 for <pgsql-novice-postgresql.org@postgresql.org>;
	Thu, 10 May 2007 05:01:05 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.241])
	by postgresql.org (Postfix) with ESMTP id DAD589FB374
	for <pgsql-novice@postgresql.org>; Thu, 10 May 2007 05:01:04 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id d17so120281and
	for <pgsql-novice@postgresql.org>; Thu, 10 May 2007 01:01:04 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	b=fLem+G1uuI+8QOqSDNSVwEQ30ISw7PkqM9cucp7HCpQevpPzm07WYWicJr2yJuT/5z9yjipQlOQcKn4iMvmDem4cRbroL2goEBOMDdI+FpL/aBsGiKyv732/9l7fsvNxAp0WGUL8BEdJQJndpkj4yVojSSDVRbMEffnucYqoA30=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=uQArWhlS5pAHKkaEzwKC/BGqnj2bBcpVwq1shX+ecR6AVecZayNje1Yg5XVAFj2ogxJAw6tGVFaKvC6rdwWAzTZzQIAhWOe5x/TRFiV/Kly0X9BQWzFnHjX6/oFWlxz3sSa0E/aaJrrY8nQibqGsNU33LUq6Eyx7tw3Yt7a4yNI=
Received: by 10.100.197.15 with SMTP id u15mr1057880anf.1178784063843;
	Thu, 10 May 2007 01:01:03 -0700 (PDT)
Received: by 10.100.130.20 with HTTP; Thu, 10 May 2007 01:01:03 -0700 (PDT)
Message-ID: <1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@mail.gmail.com>
Date: Thu, 10 May 2007 11:01:03 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org
Subject: Count rows by day interval
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_163454_28056510.1178784063797"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/52
X-Sequence-Number: 18793

------=_Part_163454_28056510.1178784063797
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Dear all,

I have the following 3 tables:

TABLE 1:  themes
 uid | theme
-----+--------
   1 | HOME
   2 | BILL
   3 | ERROR
   4 | ACTION
   5 | ANA
   6 | LIA
   7 | MIA

TABLE 2: reminder_services
  uid | theme_uid | activity_min_days | activity_max_months
-----+----------------+----------------------------+---------------------
   3 |              4 |                         10 |                   2
   1 |              1 |                           2 |                   2
   2 |              2 |                           9 |                   2
   4 |              3 |                           2 |                   2
   5 |              5 |                           4 |                   2
   6 |              6 |                           1 |                   2
   7 |              7 |                           7 |                   2

TABLE 3: sent_messages
  theme |   receiver       |             date
----------+---------------------+--------------------------------------
 MIA   | +40741775623 | 2007-04-27 09:25:00.739539+00
 MIA   | +40741775623 | 2007-04-27 09:25:05.520008+00
 MIA   | +40741775623 | 2007-04-27 09:25:09.530823+00
 MIA   | +40741775623 | 2007-04-27 09:25:11.734992+00
 MIA   | +40741775623 | 2007-04-27 09:25:13.91252+00
 LIA    | +40741775622 | 2007-04-27 09:25:19.411224+00
 LIA    | +40741775622 | 2007-04-27 09:25:21.877943+00
 LIA    | +40741775622 | 2007-04-27 09:25:23.965741+00
 LIA    | +40741775622 | 2007-04-27 09:25:25.788078+00
 LIA    | +40741775622 | 2007-04-27 09:25:27.523619+00
 LIA    | +40741775622 | 2007-04-27 09:25:29.607638+00
 LIA    | +40741775622 | 2007-04-27 09:25:31.642954+00
 LIA    | +40741775622 | 2007-04-27 09:25:33.517135+00
 LIA    | +40741775622 | 2007-04-27 09:25:35.715635+00
 LIA    | +40741775622 | 2007-04-26 09:31:35.464341+00
 LIA    | +40741775622 | 2007-04-26 09:31:38.802103+00
 LIA    | +40741775622 | 2007-04-26 09:31:41.477627+00
 LIA    | +40741775622 | 2007-04-26 09:31:43.593623+00
 LIA    | +40741775622 | 2007-04-26 09:31:46.330541+00
 LIA    | +40741775622 | 2007-04-25 09:32:12.526063+00
 LIA    | +40741775622 | 2007-04-25 09:32:14.797835+00
 LIA    | +40741775622 | 2007-04-25 09:32:17.117164+00
 LIA    | +40741775622 | 2007-04-25 09:32:19.17326+00
 LIA    | +40741775622 | 2007-04-25 09:32:21.293361+00
 MIA   | +40741775623 | 2007-05-09 06:54:46.299291+00

With the following query

 SELECT COUNT(*),
       sent_messages.theme,
       sent_messages.receiver,
       date_trunc('day',sent_messages.date)
  FROM reminder_services,
       themes,
       sent_messages
 WHERE themes.uid=reminder_services.theme_uid
   AND sent_messages.theme=themes.theme
   AND date_trunc('day',sent_messages.date) > (now() -
reminder_services.activity_max_months * INTERVAL' 1 month')
 GROUP BY sent_messages.theme, sent_messages.receiver,
date_trunc('day',sent_messages.date);

I get the result:

  count | theme |   receiver         |       date_trunc
----------+---------+----------------------+------------------------
       5 | LIA     | +40741775622 | 2007-04-26 00:00:00+00
       5 | LIA     | +40741775622 | 2007-04-25 00:00:00+00
       9 | LIA     | +40741775622 | 2007-04-27 00:00:00+00
       1 | MIA    | +40741775623 | 2007-05-09 00:00:00+00
       5 | MIA    | +40741775623 | 2007-04-27 00:00:00+00

With my query I get, for each day, the number of messages per theme and  per
receiver.
I have to have a query which returns the number of messages per theme and
per receiver
within a interval in days. The interval should be specified by
reminder_services.activity_min_days.

PS: I use the 7.4.2 version of postgres.

Please help.


Loredana

------=_Part_163454_28056510.1178784063797
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Dear all,<br>
<br>
I have the following 3 tables:<br>
<br>
TABLE 1:&nbsp;
themes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
<div style="margin-left: 40px;">&nbsp;uid |
theme&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>

-----+--------&nbsp; <br>

&nbsp;&nbsp; 1 | HOME<br>

&nbsp;&nbsp; 2 | BILL<br>

&nbsp;&nbsp; 3 | ERROR<br>

&nbsp;&nbsp; 4 | ACTION<br>

&nbsp;&nbsp; 5 | ANA<br>

&nbsp;&nbsp; 6 | LIA<br>

&nbsp;&nbsp; 7 | MIA<br>
</div>

<br>
TABLE 2: reminder_services<br>
<div style="margin-left: 40px;">
&nbsp;uid | theme_uid | activity_min_days | activity_max_months<br>

-----+----------------+----------------------------+---------------------<br>

&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; 4 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
10
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>

&nbsp;&nbsp; 1
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>

&nbsp;&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; 2
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 9
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>

&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; 3
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; 5
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>

&nbsp;&nbsp; 6 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; 6
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
&nbsp;&nbsp; 7
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
7
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
7
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
2<br>
</div>

<br>
TABLE 3: sent_messages<br>
<div style="margin-left: 40px;">
&nbsp;theme |&nbsp;&nbsp; receiver &nbsp; &nbsp; &nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
date<br>

----------+---------------------+--------------------------------------<br>

&nbsp;MIA&nbsp;&nbsp; | +40741775623 | 2007-04-27 09:25:00.739539+00<br>

&nbsp;MIA&nbsp;&nbsp; | +40741775623 | 2007-04-27 09:25:05.520008+00<br>

&nbsp;MIA&nbsp;&nbsp; | +40741775623 | 2007-04-27 09:25:09.530823+00<br>

&nbsp;MIA&nbsp;&nbsp; | +40741775623 | 2007-04-27 09:25:11.734992+00<br>

&nbsp;MIA&nbsp;&nbsp; | +40741775623 | 2007-04-27 09:25:13.91252+00<br>

&nbsp;LIA &nbsp;&nbsp; | +40741775622 | 2007-04-27 09:25:19.411224+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:21.877943+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:23.965741+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:25.788078+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:27.523619+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:29.607638+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:31.642954+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:33.517135+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-27 09:25:35.715635+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-26 09:31:35.464341+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-26 09:31:38.802103+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-26 09:31:41.477627+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-26 09:31:43.593623+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-26 09:31:46.330541+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-25 09:32:12.526063+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-25 09:32:14.797835+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-25 09:32:17.117164+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-25 09:32:19.17326+00<br>

&nbsp;LIA&nbsp; &nbsp; | +40741775622 | 2007-04-25 09:32:21.293361+00<br>

&nbsp;MIA &nbsp; | +40741775623 | 2007-05-09 06:54:46.299291+00<br>
</div>

<br>
With the following query <br>
<br>
<div style="margin-left: 40px;">
SELECT COUNT(*), <br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sent_messages.theme, <br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sent_messages.receiver, <br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date_trunc(&#39;day&#39;,sent_messages.date) <br>

&nbsp; FROM reminder_services,<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; themes,<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sent_messages <br>

&nbsp;WHERE themes.uid=reminder_services.theme_uid <br>

&nbsp;&nbsp; AND sent_messages.theme=themes.theme <br>

&nbsp;&nbsp; AND date_trunc(&#39;day&#39;,sent_messages.date) &gt; (now() - reminder_services.activity_max_months * INTERVAL&#39; 1 month&#39;) <br>

&nbsp;GROUP BY sent_messages.theme, sent_messages.receiver, date_trunc(&#39;day&#39;,sent_messages.date);<br>
</div>

<br>
I get the result:<br>
<br>
<div style="margin-left: 40px;">
&nbsp;count | theme |&nbsp;&nbsp; receiver &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date_trunc<br>

----------+---------+----------------------+------------------------<br>

&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 5 | LIA&nbsp;&nbsp; &nbsp; | +40741775622 | 2007-04-26 00:00:00+00<br>

&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 5 | LIA&nbsp; &nbsp;&nbsp; | +40741775622 | 2007-04-25 00:00:00+00<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 | LIA &nbsp;&nbsp;&nbsp; | +40741775622 | 2007-04-27 00:00:00+00<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | MIA&nbsp;&nbsp;&nbsp; | +40741775623 | 2007-05-09 00:00:00+00<br>

&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 5 | MIA&nbsp;&nbsp;&nbsp; | +40741775623 | 2007-04-27 00:00:00+00<br>
</div>
<br>
With my query I get, for each day, the number of messages per theme and&nbsp; per receiver.<br>
I have to have a query which returns the number of messages per theme and per receiver<br>
within a interval in days. The interval should be specified by reminder_services.activity_min_days.<br>
<br>
PS: I use the 7.4.2 version of postgres.<br>
<br>
Please help.<br>
<br>

<br>
Loredana<br>
<br>

------=_Part_163454_28056510.1178784063797--