Count rows by day interval

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Count rows by day interval
Date: 2007-05-10 08:01:03
Message-ID: 1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-sql

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Oliveiros Cristina 2007-05-10 11:03:30 Re: Fw: Count rows group by time intervals
Previous Message Loredana Curugiu 2007-05-10 07:15:17 Re: Fw: Count rows group by time intervals

Browse pgsql-sql by date

  From Date Subject
Next Message Louis-David Mitterrand 2007-05-10 08:05:09 Re: query to select a linked list
Previous Message Phillip Smith 2007-05-10 06:14:46 Re: Passing input to a view?