Re: 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(at)hotmail(dot)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(at)colltech(dot)com
/ /__ __/ | Voice: (512) 263-0770 x 262
/ /__/ / | Fax: (512) 263-8921
\___/ /ollective | Pager: 1-800-946-4646 # 1406217
\/echnologies |------[ http://www.colltech.com ] ------