From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: pgsql-sql@postgresql.org
To: pgsql-novice@postgresql.org
Subject: Count rows by day interval
Date: Thu, 10 May 2007 11:01:03 +0300
Message-ID: <1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@mail.gmail.com> (raw)
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
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: loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: Count rows by day interval
In-Reply-To: <1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@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