Re: Grouping Question

[Sam Mason <sam(at)samason(dot)me(dot)uk>]

On Fri, Jul 31, 2009 at 12:09:51PM -0400, Ketema Harris wrote:
> 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 ?

Either use something like date_trunc[1], convert it to a string with
to_char[2], or create a table that contains what you consider to be your
week ranges in (i.e. year, week, startdate, enddate).

-- 
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
 [2] http://www.postgresql.org/docs/current/static/functions-formatting.html