Postgres SQL Query (Generating Date Groups)

From: Mont Erickson <mont(at)airswitch(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Postgres SQL Query (Generating Date Groups)
Date: 2001-03-16 03:08:59
Message-ID: 3AB183CB.4151D56D@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

First of all, I apologize if I have posted to the wrong group...

Here's the question.

What would be the proper method, given the following table, to generate,
with a single select query, a date grouped output:

table_a

cust_id | usage_date | bytes
--------+------------+-------
1 | 2001-03-01 | 2578
1 | 2001-03-02 | 1234
2 | 2001-03-01 | 12345
1 | 2001-03-05 | 2578
1 | 2001-03-07 | 1234
2 | 2001-03-06 | 12345

etc etc etc...

I'm trying for output that would look something like this:

cust_id | period_1 | period_2 | period_3
--------+----------+----------+----------
1 | 12345678 | 457892 | 98765
2 | 10734 | 1037 | 8709

...etc etc etc...

I have tried writing the query along the lines of the following, but
based on the results I'm seeing, I'm on the wrong track:

select
cust_id,
case when
date_part('day',usage_date) >= 1 and date_part('day',usage_date)
<= 7
THEN sum(sum) END as period_1,
case when
date_part('day',usage_date) >= 2 and date_part('day',usage_date)
<= 8
THEN sum(sum) END as period_2,
case when
date_part('day',usage_date) >= 3 and date_part('day',usage_date)
<= 9
THEN sum(sum) END as period_3
from table_a
group by
cust_id

Is this possible under Postgres? What can I do to accomplish this
without writing a separate select for each date period? Thank you in
advance!

Mont Erickson
ns_monterickson(at)hotmail(dot)com (remove "ns_" to reply)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dao Hai Ninh 2001-03-16 03:47:33
Previous Message Tatsuo Ishii 2001-03-16 01:24:57 Re: shared memory settings: SHMMAX and SHMALL