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

Search Discussions

  • Sam Mason at Jul 31, 2009 at 4:16 pm

    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).
  • Gerhard Heift at Jul 31, 2009 at 4:47 pm

    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
  • Rob Wickert at Jul 31, 2009 at 4:55 pm
    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 ?
  • Ketema Harris at Jul 31, 2009 at 5:02 pm
    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 ?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 31, '09 at 4:10p
activeJul 31, '09 at 5:02p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase