Re: Grouping Question

On Fri, Jul 31, 2009 at 12:09:51PM -0400, Ketema Harris wrote:
> I have a table defined as
>
> CREATE TABLE mytable
> (
> carrier varchar,
> start_time timestamp with time zone,
> call_date date,
> cost numeric,
> call_length numeric
> )
>
> I want to create a query that will generate a the following columns:

select carrier,
extract(YEAR FROM start_time) || '-' || EXTRACT(WEEK FROM start_time),
sum(call_length) as totallength, sum(cost) as total_cost
from mytable
group by carrier,
extract(YEAR FROM start_time) || '-' || EXTRACT(WEEK FROM start_time),
order by carrier,
extract(YEAR FROM start_time) || '-' || EXTRACT(WEEK FROM start_time),
>
> week is defined as a date range so something like 7/6/2009 - 7/13/2009
>
> I would need the timestamps to be grouped into 7 day intervals
> starting from the first one and moving through the table. is this
> possible in a single query or would I have to write a function ?

Regards,
Gerhard