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: | Whole Thread | Raw Message | 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)
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 |