Skip site navigation (1) Skip section navigation (2)

Re: Count rows group by time intervals

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Count rows group by time intervals
Date: 2007-05-09 13:44:17
Message-ID: 004901c79240$2377e250$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
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 ----- 
  From: Loredana Curugiu 
  To: pgsql-novice(at)postgresql(dot)org 
  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. 




In response to

pgsql-novice by date

Next:From: Oliveiros CristinaDate: 2007-05-09 13:46:34
Subject: Fw: Count rows group by time intervals
Previous:From: Kevin HunterDate: 2007-05-09 13:39:21
Subject: Re: Count rows group by time intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group