Fw: Count rows group by time intervals
| От | Oliveiros Cristina |
|---|---|
| Тема | Fw: Count rows group by time intervals |
| Дата | |
| Msg-id | 005501c79240$7544bdb0$ec5a3d0a@marktestcr.marktest.pt обсуждение |
| Ответы |
Re: Fw: Count rows group by time intervals
|
| Список | pgsql-novice |
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"
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 -----
From: Oliveiros Cristina
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"
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 CurugiuSent: Wednesday, May 09, 2007 1:07 PMSubject: [NOVICE] Count rows group by time intervalsDear 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 по дате отправления: