Grouping Question

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]http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC, convert it to a string with
to_char[2]http://www.postgresql.org/docs/current/static/functions-formatting.html, 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

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

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:

OK that worked, but now how do I get the integer returned by extract
into a human friendly string like
7/1 - 7/8 or something similar ?

On Jul 31, 2009, at 12:37 PM, Rob Wickert wrote: