Re: Grouping Question - Mailing list pgsql-general

From Sam Mason
Subject Re: Grouping Question
Date
Msg-id 20090731161550.GL5407@samason.me.uk
Whole thread Raw
In response to Grouping Question  (Ketema Harris <ketema@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Raymond O'Donnell
Date:
Subject: Re: export csv file on XP
Next
From: Thomas Kellerer
Date:
Subject: Re: export csv file on XP