Re: Count rows group by time intervals

> Another way is to use an auxillary table to join on your required date
> range.
>
> SELECT A.theme, A.receiver, COUNT(A.date),
> Time_range.date_start, Time_range.date_end
> FROM Your_table A
> INNER JOIN ( SELECT ( SELECT MIN( date ) FROM Your_table ) + x * INTERVAL
> '1 day' AS date_start,
> ( SELECT MIN( date ) FROM Your_table ) + 2 + x *
> INTERVAL '1 day' AS date_end,
> FROM Generate_series( 1, ( SELECT MAX( date ) FROM
> Your_table ) -
> ( SELECT MIN( date ) FROM
> Your_table ), 2 )
> ) AS Time_range( date_start, date_end )
> ON B.date_start <= A.date AND B.date_end > A.date
> GROUP BY A.theme, A.receiver, Time_range.date_start,
> Time_range.date_end;

Hi Richard,

I think your solution is very good and elegant, but I cannot call
generate_series()
because in the 7.4.2 version of postgres ( I use this version ) this
function doesn't
exist.

Many thanks,
Loredana