Home > mailing lists

Count rows by day interval - Mailing list pgsql-sql

From	Loredana Curugiu
Subject	Count rows by day interval
Date	May 10, 2007 08:01:20
Msg-id	1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@mail.gmail.com Whole thread Raw
Responses	Re: Count rows by day interval
List	pgsql-sql

Tree view

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

pgsql-sql by date:

From: "Phillip Smith"
Date: 10 May 2007, 06:15:05
Subject: Re: Passing input to a view?

From: Louis-David Mitterrand
Date: 10 May 2007, 08:05:22
Subject: Re: query to select a linked list

Count rows by day interval - Mailing list pgsql-sql

Previous

Next