Re: Grouping Question
От | Ketema Harris |
---|---|
Тема | Re: Grouping Question |
Дата | |
Msg-id | 405F8863-AA9C-4955-9447-CEDB4B2657E8@midnightoilconsulting.com обсуждение исходный текст |
Ответ на | Grouping Question (Ketema Harris <ketema@gmail.com>) |
Список | pgsql-general |
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 ?
В списке pgsql-general по дате отправления: