Postgres Pro
Downloads
Home   >   mailing lists

Fw: Count rows group by time intervals - Mailing list pgsql-novice

From Oliveiros Cristina
Subject Fw: Count rows group by time intervals
Date
Msg-id 005501c79240$7544bdb0$ec5a3d0a@marktestcr.marktest.pt
Whole thread Raw
Responses Re: Fw: Count rows group by time intervals  ("Loredana Curugiu" <loredana.curugiu@gmail.com>)
List pgsql-novice
Tree view
Parents missing, sorry :-)
 
SELECT  a."date",a."theme",a.receiver,COUNT(*)
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR ((b."date" - a."date") = 0))
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
----- Original Message -----
Sent: Wednesday, May 09, 2007 2:44 PM
Subject: Re: [NOVICE] Count rows group by time intervals

Howdy, Loredana.
 
You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
 
Please try something like this. I am not sure if it works, because I don't have a table like yours.
I am assuming  your  table is called table
 
SELECT  a."date",a."theme",a.receiver,COUNT(*)
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
 
 
Then tell me if it worked
 
Cheers,
Oliveiros
 
----- Original Message -----
Sent: Wednesday, May 09, 2007 1:07 PM
Subject: [NOVICE] Count rows group by time intervals

Dear all,

I have the following table:

theme |   receiver        |           date
---------+----------------------+------------------------
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-27 00:00:00+00
 LIA   | +40741775622 |  2007-04-26 00:00:00+00
 LIA   | +40741775622 |  2007-04-26 00:00:00+00
 LIA   | +40741775622 |  2007-04-26 00:00:00+00
 LIA   | +40741775622 |  2007-04-26 00:00:00+00
 LIA   | +40741775622 |  2007-04-26 00:00:00+00
 LIA   | +40741775622 |  2007-04-25 00:00:00+00
 LIA   | +40741775622 |  2007-04-25 00:00:00+00
 LIA   | +40741775622 |  2007-04-25 00:00:00+00
 LIA   | +40741775622 |  2007-04-25 00:00:00+00
 LIA   | +40741775622 |  2007-04-25 00:00:00+00
 MIA   | +40741775622 | 2007-04-27 00:00:00+00
 MIA   | +40741775622 | 2007-04-27 00:00:00+00
 MIA   | +40741775622 | 2007-04-27 00:00:00+00
 MIA   | +40741775622 | 2007-04-27 00:00:00+00
 MIA   | +40741775622 |  2007-04-27 00:00:00+00
 MIA   | +40741775622 |  2007-05-09 00:00:00+00

I would like to count rows group by theme, receiver, and time intervals of
two days. I don't know how to start.

Hope that somebody could help me.




pgsql-novice by date:

Previous
From: "Oliveiros Cristina"
Date:
Subject: Re: Count rows group by time intervals
Next
From: "Loredana Curugiu"
Date:
Subject: Re: Fw: Count rows group by time intervals