Search Postgresql Archives

Re: Grouping Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ?



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux