Postgres SQL Query (Generating Date Groups)

Mont, you have probably gotten an answer on this already, but I haven't
seen one go across the list, so here goes.

Assuming that I understand what you want, the easiest way that I can
see to do it is by using self joins.

ie:

SELECT a.cust_id ,
sum(a.bytes) as period_1,
sum(b.bytes) as period_2,
sum(c.bytes) as period_3
sum(d.bytes) as period_4,
sum(e.bytes) as period_5
sum(f.bytes) as period_6
FROM table_a a, table_a b, table_a c, table_a d, table_a e, table_a f
WHERE a.cust_id = b.cust_id AND
b.cust_id = c.cust_id AND
c.cust_id = d.cust_id AND
d.cust_id = e.cust_id AND
e.cust_id = f.cust_id AND
a.usage_date = '2001-03-01' AND
b.usage_date = '2001-03-02' AND
c.usage_date = '2001-03-03' AND
d.usage_date = '2001-03-05' AND
e.usage_date = '2001-03-06' AND
f.usage_date = '2001-03-07'
GROUP BY a.cust_id;

This will work, but you will have to hardcode the query each time for the
number of your periods.

I hope that this helps.

--
Bill

On Thu, Mar 15, 2001 at 08:08:59PM -0700, Mont Erickson wrote:

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@hotmail.com (remove "ns_" to reply)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

--
_____
/ ___/___ | Bill Huff / bhuff@colltech.com
/ /__ __/ | Voice: (512) 263-0770 x 262
/ /__/ / | Fax: (512) 263-8921
\___/ /ollective | Pager: 1-800-946-4646 # 1406217
\/echnologies |------[ http://www.colltech.com ] ------