Thread: Grouping Question

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 ?

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

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:
> 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 ?
>
>

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:

> 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 ?