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

Re: Fw: Count rows group by time intervals

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)gmail(dot)com>
To: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Fw: Count rows group by time intervals
Date: 2007-05-09 14:50:50
Message-ID: f54607780705090750v6989abdcm99b9e798496bb0f4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Funny thing.
In mine, it works...
On mine, If I subtract one date  from the other I get the number of days in
between...which is another integer, I guess. and '%'  is the modular
operator. Doesnt it work like this on yours?

Which version are you using?

try

SELECT (date - '2007-01-01') % 2
FROM table

to see if you get zeroes and ones .

Cheers,
Oliveiros

2007/5/9, Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>:
>
>
> > You need a query that returns the number of receivers on each theme, in
> > two days intervals, is this correct?
> >
>
> yes, this is correct. Actually the interval in days is variable ( it would
> specified using PreparedStatemtent ).
> Sorry I didn't say from the begining.
>
> 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"
> >
>
> The query above it's not working, I've got the following error:
>      ERROR:  operator does not exist: interval % integer
>      HINT:  No operator matches the given name and argument type(s). You
> may need to add explicit type casts.
>
>
>
>

In response to

Responses

pgsql-novice by date

Next:From: Loredana CurugiuDate: 2007-05-09 15:01:03
Subject: Re: Fw: Count rows group by time intervals
Previous:From: Loredana CurugiuDate: 2007-05-09 14:29:49
Subject: Re: Fw: Count rows group by time intervals

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