Re: Grouping Question

[Rob Wickert <rob(at)wordtracker(dot)com>]

You would use some of postgresql built in date functions to extract the week from the date and group 
by that

i.e.

select carrier, extract(week from start_time) as week, sum(call_length) as totallength, sum(cost) as 
total_cost
from mytable
group by carrier, extract(week from start_time)
order by week, carrier

you'll probably want to extract the year as well so dates weeks from alternate years don't get 
merged together

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:
>
> carrier, week, sum(call_length) as totallength, sum(cost) as total_cost
> from mytable
> group by carrier, (WHAT HERE?)
> order by week, carrier
>
>
> 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 ?